May 2, 2008 at 12:38 am
I’m trying to optimize existing C++ application running on SQL 2000 ready for migration to 2005. I have a query that executes every time I need to find if a specific product is available. Sometimes there are many products selected in the same time (up to 40 -50) and then the query executes 40-50 times. In this case I would like to combine all these calls to the database in a single query. Of course, I do not know in advance how many products will be selected by the user, so I have to dynamically construct my statement.
This is all good – but I wonder what will be the optimum SQL statement to do that because the product table is big – more then 7 mln records.
My query has the following structure:
Select col1, col2, col3, col4 from products where product_name = ‘xxx’ and product_type = 2;
So, I thought of using OR , or even better UNION structures to accomplice this, or may be to create some type of function/procedure to return my result set and I was wondering which one will perform better.
I did similar query using a function with 1 parameter and IN clause (passing the parameter with the comma, splitting on the comma, and joining on the function only with the values that I need (not the whole table). That works great with songle paramater but I have troubles implementing this with pair of values.
I know you may say “why don’t you try it and find out”, but I’m looking also for some guidelines on the best practices, and may be some examples.
Thanks a lot for the help,
Tom
May 2, 2008 at 2:24 am
So what would be an example of the user inputs? Several pairs of product name and product type (potentially)?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 2, 2008 at 3:09 am
It's simpler to do this with a join to a temporary table, and will probably run much faster.
-- declare value pair table
declare @values table (col1 int not null, col2 int not null)
-- Insert paramter pairs into value pair table
insert into @values
select col1 = 1, col2 = 3 union all
select col1 = 2, col2 = 4 union all
...
select col1 = 77, col2 = 99
-- Select from table with join to value pair table
select
*
from
MyTable a
inner join
@values b
ona.col1 = b.col1 and
a.col2 = b.col2
May 2, 2008 at 3:45 am
I agree with Michael. This is the example I knocked up, but I was waiting for a reply. I also wanted to know if 'pairs' could have one of them as null or not. The example assumes they can because the alternative is simpler.
You also have to overcome the problem of getting the filter criteria from the front-end to SQL server. In this case, I would use a stored procedure with a single xml parameter. Anyway, here's a snippet...
-- Structure and data
declare @products table (col1 int, product_name varchar(10), product_type int)
insert @products
select 1, 'Product A', 1
union all select 2, 'Product B', 1
union all select 3, 'Product C', 3
union all select 4, 'Product D', 2
union all select 5, 'Product E', 1
union all select 6, 'Product F', 2
union all select 7, 'Product G', 3
union all select 8, 'Product H', 1
union all select 9, 'Product I', 3
union all select 10, 'Product J', 2
-- Inputs
declare @filterXml xml
set @filterXml = '
<root>
<product name="Product A" type="1" />
<product name="Product D" type="2" />
<product name="Product I" type="3" />
<product type="2" />
</root>'
-- Calculation
declare @filter table (product_name varchar(10), product_type int)
insert @filter
select
x.product.value('@name[1]', 'varchar(10)') AS product_name,
x.product.value('@type[1]', 'int') AS product_type
from @filterXml.nodes('//root/product') as x(product)
select a.col1, a.product_name, a.product_type from @products a where exists
(select * from @filter where
(product_name = a.product_name or product_name is null) and
(product_type = a.product_type or product_type is null))
/* Results
col1 product_name product_type
----------- ------------ ------------
1 Product A 1
4 Product D 2
9 Product I 3
6 Product F 2
10 Product J 2
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 2, 2008 at 12:57 pm
This is exactly what I was looking for.
How can I incorporated into a SP that will return also the result set?
Thanks so much for the help,
Tom
May 7, 2008 at 1:51 am
mjschwenger (5/2/2008)
This is exactly what I was looking for.How can I incorporated into a SP that will return also the result set?
Thanks so much for the help,
Tom
Just like any other SP...
-- Structure and data
create table products (col1 int, product_name varchar(10), product_type int)
insert products
select 1, 'Product A', 1
union all select 2, 'Product B', 1
union all select 3, 'Product C', 3
union all select 4, 'Product D', 2
union all select 5, 'Product E', 1
union all select 6, 'Product F', 2
union all select 7, 'Product G', 3
union all select 8, 'Product H', 1
union all select 9, 'Product I', 3
union all select 10, 'Product J', 2
go
-- Stored Procecure
create proc myProc @filterXml xml as
declare @filter table (product_name varchar(10), product_type int)
insert @filter
select
x.product.value('@name[1]', 'varchar(10)') AS product_name,
x.product.value('@type[1]', 'int') AS product_type
from @filterXml.nodes('//root/product') as x(product)
select a.col1, a.product_name, a.product_type from products a where exists
(select * from @filter where
(product_name = a.product_name or product_name is null) and
(product_type = a.product_type or product_type is null))
go
exec myProc ' <root>
<product name="Product A" type="1" />
<product name="Product D" type="2" />
<product name="Product I" type="3" />
<product type="2" />
</root>'
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply