Two queries versus one combined query

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

Re: Two queries versus one combined query

Adrienne Boswell wrote:
> 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.

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?

> I am using MS SQL.

What version?


> Any thoughts? I am correct that the combined query is better in the
> long run?

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?

> 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.
>
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.

Re: Two queries versus one combined query

Bob Barrows [MVP] wote:
> Adrienne Boswell wrote:
> > 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.
>
> 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?
>

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

> > I am using MS SQL.
>
> What version?
>

8.0

>
> > Any thoughts? I am correct that the combined query is better in the
> > long run?
>
> 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?

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!

>
> > 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.
> >
> 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
>

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.