How much memory and CPU resources would this query use?

[dickbaker]

I'm updating pages in advance of converting to an ecommerce site. I have about 1,000 pages of products. On each page I'll be putting a database connection and this query:

select table1.itemNum from dbo.table1, dbo.table2 where (table1.ItemNumber= '123' and table1.Quantity= '0') AND (table2.ItemName = 'Acme' and table2.Quant = '0')

Table 1 and Table 2 have the inventory from two different distributors, and I'm looking to show a "Buy" button and and "in stock" message of the query returns no data if one or both distributors has the item in stock, or doesn't show the "Buy" button and shows an "out of stock" message both distributors' tables show 0 for a quantity.

Each table has just two columns and about 15,000 rows. Again, about 1,000 pages will have this query.

Should I put these tables into a separate database, or will the queries use low enough resources that I can add the tables to another database without slowing down my site?

Thanks for any replies.

[Dabrowski]

With tables that small and only 15,000 rows I think you'll be fine, but there are a couple of ways you can optimise this....

Either: You're specifically looking up quantity 0. Why not just look up the item number, then you can display the quantity in stock if required, plus it's 2 less work for the query.

Or: I had a database with around 500,000 rows. I was performing a lookup, like you, on 2 columns. The development server I was on at the time, a P3 700 with less than 512Mb ram. This was taking some time, so I added an index. An index creates an active catalog in memory, based on the 2 columns, so queries like this can be performed in a fraction of the time.

Hope this helps.