|
|
You're in the
sql server programming
group:performance issue
sql server programming:
I am using follwing query to get some data back from sql server 2000... select @Rec_Cnt = count(Customer_Id) FROM products with (nolock) WHERE product1 like COALESCE(@product1, product1) and product2 like COALESCE(@product2, product2) and product3 Like COALESCE(@product3, product3) and product4 Like COALESCE(@product4, product4) and product5 = COALESCE(@product5, product5) and ((product6 = @product6 ) or (@product6 Is Null)) And product7 = @product7 I am using count and bunch of like statements...There is no problem in query...I have around 8 millions rows....it is taking around 2 minutes to run above query... what is the best way to improve above query...or there is some other way to write above code...need to improve the performance. I have index defined on customerid, product1, product2, product3 and product4. Thanks
Your indexes are typically not usable for LIKE clauses. Try text
indexing. Also, short of text indexing, I would phrase this query more like... WHERE (@product1 IS NULL OR product1 LIKE @product1) AND (@product2 IS NULL OR product2 LIKE @product1) .... Not knowing your data, I wouldn't know what else to suggest, but I wonder why the LIKE clauses. Do you expect your user to always enter % signs to search for substrings? Otherwise, the LIKE is more like an =. [quoted text, click to view] On Jun 29, 7:34 pm, shanky <sha...@discussions.microsoft.com> wrote: > I have one performance problem.... > > I am using follwing query to get some data back from sql server 2000... > > select @Rec_Cnt = count(Customer_Id) > FROM products with (nolock) > WHERE product1 like COALESCE(@product1, product1) and > product2 like COALESCE(@product2, product2) and > product3 Like COALESCE(@product3, product3) and > product4 Like COALESCE(@product4, product4) and > product5 = COALESCE(@product5, product5) and > ((product6 = @product6 ) or (@product6 Is Null)) And > product7 = @product7 > > I am using count and bunch of like statements...There is no problem in > query...I have around 8 millions rows....it is taking around 2 minutes to run > above query... > what is the best way to improve above query...or there is some other way to > write above code...need to improve the performance. I have index defined on > customerid, product1, product2, product3 and product4. > > Thanks
How do i use text indexing. Do you think Text indexing will reduce the time.
Yes, user can enter any string.....for example for product1 user can enter 'a' or 's'...i need to display back all the products matching with 'a' and 's' ...i do use like 'a%' or 's%'....i will get rid of COALESCE like you suggested..let see if it makes any difference..... Thanks for your reply... [quoted text, click to view] "tallpeak@gmail.com" wrote:
> Your indexes are typically not usable for LIKE clauses. Try text > indexing. > > Also, short of text indexing, I would phrase this query more like... > > WHERE (@product1 IS NULL OR product1 LIKE @product1) > AND (@product2 IS NULL OR product2 LIKE @product1) > .... > > Not knowing your data, I wouldn't know what else to suggest, but I > wonder why the LIKE clauses. Do you expect your user to always enter % > signs to search for substrings? Otherwise, the LIKE is more like an =. > > > > On Jun 29, 7:34 pm, shanky <sha...@discussions.microsoft.com> wrote: > > I have one performance problem.... > > > > I am using follwing query to get some data back from sql server 2000... > > > > select @Rec_Cnt = count(Customer_Id) > > FROM products with (nolock) > > WHERE product1 like COALESCE(@product1, product1) and > > product2 like COALESCE(@product2, product2) and > > product3 Like COALESCE(@product3, product3) and > > product4 Like COALESCE(@product4, product4) and > > product5 = COALESCE(@product5, product5) and > > ((product6 = @product6 ) or (@product6 Is Null)) And > > product7 = @product7 > > > > I am using count and bunch of like statements...There is no problem in > > query...I have around 8 millions rows....it is taking around 2 minutes to run > > above query... > > what is the best way to improve above query...or there is some other way to > > write above code...need to improve the performance. I have index defined on > > customerid, product1, product2, product3 and product4. > > > > Thanks > >
For this query, the only usuable index would be an index on
products(product7). Some general remarks: it is a bit odd that you do "LIKE COALESCE" four times, "= COALESCE" one time and the equivalent of "= COALESCE" one time. By the way: changing "prod = COALESCE(@prod,prod)" to "prod = @prod OR @prod IS NULL" will not make a difference. What can make a difference is if you rewrite the "LIKE COALESCE(@prod,prod)" to "LIKE COALESCE(@prod,'%')", but only for product that you mention first in the index. So I suggest that you do that for the column where it is most likely that you have filled the local variable for. So you are now always using a value in @product7. Let's assume that the next local variable that is likely to be filled is @product6. In that case, you could create an index on products(product7,product6), and rewrite the last two lines of your query to AND product6 LIKE COALESCE(@product6, '%') AND product7 = @product7 If the stored procedure is recompiled each time it is called (WITH RECOMPILE) that you could do this for all combination of product7 + another productcolumn. In the end, it will probably still give you just average performance. If you want better performance, read articles like this: http://www.sommarskog.se/dynamic_sql.html HTH, Gert-Jan [quoted text, click to view] shanky wrote:
> > I have one performance problem.... > > I am using follwing query to get some data back from sql server 2000... > > select @Rec_Cnt = count(Customer_Id) > FROM products with (nolock) > WHERE product1 like COALESCE(@product1, product1) and > product2 like COALESCE(@product2, product2) and > product3 Like COALESCE(@product3, product3) and > product4 Like COALESCE(@product4, product4) and > product5 = COALESCE(@product5, product5) and > ((product6 = @product6 ) or (@product6 Is Null)) And > product7 = @product7 > > I am using count and bunch of like statements...There is no problem in > query...I have around 8 millions rows....it is taking around 2 minutes to run > above query... > what is the best way to improve above query...or there is some other way to > write above code...need to improve the performance. I have index defined on > customerid, product1, product2, product3 and product4. >
Is there a way to just count like 200 rows...instead of doing count on 8
million records....count is taking 2-3 minutes.... [quoted text, click to view] "Gert-Jan Strik" wrote:
> For this query, the only usuable index would be an index on > products(product7). > > Some general remarks: it is a bit odd that you do "LIKE COALESCE" four > times, "= COALESCE" one time and the equivalent of "= COALESCE" one > time. By the way: changing "prod = COALESCE(@prod,prod)" to "prod = > @prod OR @prod IS NULL" will not make a difference. > > What can make a difference is if you rewrite the "LIKE > COALESCE(@prod,prod)" to "LIKE COALESCE(@prod,'%')", but only for > product that you mention first in the index. So I suggest that you do > that for the column where it is most likely that you have filled the > local variable for. > > So you are now always using a value in @product7. Let's assume that the > next local variable that is likely to be filled is @product6. In that > case, you could create an index on products(product7,product6), and > rewrite the last two lines of your query to > > AND product6 LIKE COALESCE(@product6, '%') > AND product7 = @product7 > > If the stored procedure is recompiled each time it is called (WITH > RECOMPILE) that you could do this for all combination of product7 + > another productcolumn. > > In the end, it will probably still give you just average performance. If > you want better performance, read articles like this: > http://www.sommarskog.se/dynamic_sql.html > > HTH, > Gert-Jan > > > shanky wrote: > > > > I have one performance problem.... > > > > I am using follwing query to get some data back from sql server 2000... > > > > select @Rec_Cnt = count(Customer_Id) > > FROM products with (nolock) > > WHERE product1 like COALESCE(@product1, product1) and > > product2 like COALESCE(@product2, product2) and > > product3 Like COALESCE(@product3, product3) and > > product4 Like COALESCE(@product4, product4) and > > product5 = COALESCE(@product5, product5) and > > ((product6 = @product6 ) or (@product6 Is Null)) And > > product7 = @product7 > > > > I am using count and bunch of like statements...There is no problem in > > query...I have around 8 millions rows....it is taking around 2 minutes to run > > above query... > > what is the best way to improve above query...or there is some other way to > > write above code...need to improve the performance. I have index defined on > > customerid, product1, product2, product3 and product4. > > > > Thanks
Maybe.
You could try something like this: SELECT @Rec_Cnt = COUNT(Customer_Id) FROM ( SELECT TOP 200 * FROM products WITH (nolock) ... ) AS T But you will still need good indexes, otherwise it will probably not improve performance. And you need the inner query to use a good index, and for that you might need Dynamic SQL (the link I posted before). Gert-Jan [quoted text, click to view] shanky wrote:
> > Is there a way to just count like 200 rows...instead of doing count on 8 > million records....count is taking 2-3 minutes.... > > "Gert-Jan Strik" wrote: > > > For this query, the only usuable index would be an index on > > products(product7). > > > > Some general remarks: it is a bit odd that you do "LIKE COALESCE" four > > times, "= COALESCE" one time and the equivalent of "= COALESCE" one > > time. By the way: changing "prod = COALESCE(@prod,prod)" to "prod = > > @prod OR @prod IS NULL" will not make a difference. > > > > What can make a difference is if you rewrite the "LIKE > > COALESCE(@prod,prod)" to "LIKE COALESCE(@prod,'%')", but only for > > product that you mention first in the index. So I suggest that you do > > that for the column where it is most likely that you have filled the > > local variable for. > > > > So you are now always using a value in @product7. Let's assume that the > > next local variable that is likely to be filled is @product6. In that > > case, you could create an index on products(product7,product6), and > > rewrite the last two lines of your query to > > > > AND product6 LIKE COALESCE(@product6, '%') > > AND product7 = @product7 > > > > If the stored procedure is recompiled each time it is called (WITH > > RECOMPILE) that you could do this for all combination of product7 + > > another productcolumn. > > > > In the end, it will probably still give you just average performance. If > > you want better performance, read articles like this: > > http://www.sommarskog.se/dynamic_sql.html > > > > HTH, > > Gert-Jan > > > > > > shanky wrote: > > > > > > I have one performance problem.... > > > > > > I am using follwing query to get some data back from sql server 2000... > > > > > > select @Rec_Cnt = count(Customer_Id) > > > FROM products with (nolock) > > > WHERE product1 like COALESCE(@product1, product1) and > > > product2 like COALESCE(@product2, product2) and > > > product3 Like COALESCE(@product3, product3) and > > > product4 Like COALESCE(@product4, product4) and > > > product5 = COALESCE(@product5, product5) and > > > ((product6 = @product6 ) or (@product6 Is Null)) And > > > product7 = @product7 > > > > > > I am using count and bunch of like statements...There is no problem in > > > query...I have around 8 millions rows....it is taking around 2 minutes to run > > > above query... > > > what is the best way to improve above query...or there is some other way to > > > write above code...need to improve the performance. I have index defined on > > > customerid, product1, product2, product3 and product4. > > > > > > Thanks
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |