I have a classic asp page that lists products. Right now, there is
one query to get the list of products (from a product view), and then
another query to get the individual merchant's information (from a
vendor view). I am thinking that using one query for both is going to
save some server time because it's only one trip to the db. In my
testing, however, it seems that the time to do both is .00 and to do a
combined query is .01.

I am using MS SQL.

Here's my original query:

SELECT product_ipk, product_name, displaytext, image_path,
description, price, vendor_name, vendor_ipk,
city_name, category_description, classification_name, discounttype
FROM view_product WITH(NOEXPAND)
WHERE vendor_ipk = 1581365
AND city_id IN (18399)
ORDER BY product_ipk

Here is my proposed query:

SELECT product_ipk, product_name, displaytext, p.image_path,
p.description, price, v.vendor_name, p.vendor_ipk, p.city_name,
p.category_description, p.classification_name, p.discounttype,
v.street_no, v.street_name, v.location, v.state_abbr, v.phone1a,
v.phone1n, v.email, v.url, v.dispemail
FROM view_product p WITH(NOEXPAND)
JOIN view_vendor_default v ON v.vendor_ipk = p.vendor_ipk
WHERE category_ipk = 110
AND p.city_id IN (18399)
ORDER BY product_ipk

Any thoughts? I am correct that the combined query is better in the
long run? I don't think I can make a sp out of this because the user
can choose sorting, and can also include a keyword search.

TIA
--
Adrienne Boswell at work
Administrator nextBlock.com
http://atlas.nextblock.com/files/
Please respond to the group so others can share

Bob Barrows [MVP] wote:
[quoted text, click to view]

Yup, from QA, and I'll bet that 00 started life as .0099

[quoted text, click to view]

8.0

[quoted text, click to view]

I ran it again so that it would get more records. The combined query
for 118 records used cumulative client processing of 45, and the
single with the second query was 74.

My question is answered. Thank you!

[quoted text, click to view]

That's too complicated for me right now. Slowly but surely the fog is
lifting in my brain and when it does, that will also make sense.

[quoted text, click to view]

Where are you getting these timing numbers? From Query Analyzer? Are
these values in seconds?
Is there a rounding or truncation issue? Could .00 have started life as
..0099?

[quoted text, click to view]

What version?

<snip>
[quoted text, click to view]

Frankly it would never even have occurred to me not to do a join and
bring the data back all at once. Depending on your answers to the
previous questions, I would investigate why the join is so much slower
than the non-join. Are the tables properly indexed? Have you used the
Index Analyzer (Tuning Wizard) to verify?

[quoted text, click to view]
Actually you can, although performance may suffer depending on the
technique you use. See Erland's articles on dynamic search criteria and
dynamic sorting at http://www.sommarskog.se/index.html

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.