More efficient query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

More efficient query

Author

Topic

cda2007
Starting Member

17 Posts

Posted - 2010-03-08 : 18:17:47

Hello.
I have the next query for retrieving the product id adding a number of parameters.
How can I make this more efficient(use fewer resources) for the database.
Thank you

ALTER PROCEDURE [dbo].[test1]
(@pSpecNume_ID1 int = NULL,
@pSpecNume_ID2 int = NULL,
@pSpecNume_ID3 int = NULL,
@pSpecNume_ID4 int = NULL,
@pSpecNume_ID5 int = NULL,
@pSpecNume_ID6 int = NULL,
@pSpecNume_ID7 int = NULL,

@pSpecValori_ID1 int = NULL,
@pSpecValori_ID2 int = NULL,
@pSpecValori_ID3 int = NULL,
@pSpecValori_ID4 int = NULL,
@pSpecValori_ID5 int = NULL,
@pSpecValori_ID6 int = NULL,
@pSpecValori_ID7 int = NULL)
AS

----declare table
DECLARE @ProduseFiltre table
(ProdusID INT,
Filtru1 INT,
Filtru2 INT,
Filtru3 INT,
Filtru4 INT,
Filtru5 INT,
Filtru6 INT,
Filtru7 INT)

Insert into @ProduseFiltre
SELECT Produs_ID,
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID1 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID2 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID3 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID4 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID5 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID6 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID7 THEN pSpecValori_ID END)
FROM Produs_Specificatii
GROUP BY Produs_ID

SELECT ProdusID, Filtru1, Filtru2, Filtru3, Filtru4, Filtru5, Filtru6, Filtru7 from @ProduseFiltre
WHERE (@pSpecValori_ID1 IS NULL OR (Filtru1 = @pSpecValori_ID1 OR Filtru2 = @pSpecValori_ID1 OR Filtru3 = @pSpecValori_ID1 OR Filtru4 = @pSpecValori_ID1 OR Filtru5 = @pSpecValori_ID1 OR Filtru6 = @pSpecValori_ID1 OR Filtru7 = @pSpecValori_ID1))
and (@pSpecValori_ID2 IS NULL OR (Filtru1 = @pSpecValori_ID2 OR Filtru2 = @pSpecValori_ID2 OR Filtru3 = @pSpecValori_ID2 OR Filtru4 = @pSpecValori_ID2 OR Filtru5 = @pSpecValori_ID2 OR Filtru6 = @pSpecValori_ID2 OR Filtru7 = @pSpecValori_ID2))
and (@pSpecValori_ID3 IS NULL OR (Filtru1 = @pSpecValori_ID3 OR Filtru2 = @pSpecValori_ID3 OR Filtru3 = @pSpecValori_ID3 OR Filtru4 = @pSpecValori_ID3 OR Filtru5 = @pSpecValori_ID3 OR Filtru6 = @pSpecValori_ID3 OR Filtru7 = @pSpecValori_ID3))
and (@pSpecValori_ID4 IS NULL OR (Filtru1 = @pSpecValori_ID4 OR Filtru2 = @pSpecValori_ID4 OR Filtru3 = @pSpecValori_ID4 OR Filtru4 = @pSpecValori_ID4 OR Filtru5 = @pSpecValori_ID4 OR Filtru6 = @pSpecValori_ID4 OR Filtru7 = @pSpecValori_ID4))
and (@pSpecValori_ID5 IS NULL OR (Filtru1 = @pSpecValori_ID5 OR Filtru2 = @pSpecValori_ID5 OR Filtru3 = @pSpecValori_ID5 OR Filtru4 = @pSpecValori_ID5 OR Filtru5 = @pSpecValori_ID5 OR Filtru6 = @pSpecValori_ID5 OR Filtru7 = @pSpecValori_ID5))
and (@pSpecValori_ID6 IS NULL OR (Filtru1 = @pSpecValori_ID6 OR Filtru2 = @pSpecValori_ID6 OR Filtru3 = @pSpecValori_ID6 OR Filtru4 = @pSpecValori_ID6 OR Filtru5 = @pSpecValori_ID6 OR Filtru6 = @pSpecValori_ID6 OR Filtru7 = @pSpecValori_ID6))
and (@pSpecValori_ID7 IS NULL OR (Filtru1 = @pSpecValori_ID7 OR Filtru2 = @pSpecValori_ID7 OR Filtru3 = @pSpecValori_ID7 OR Filtru4 = @pSpecValori_ID7 OR Filtru5 = @pSpecValori_ID7 OR Filtru6 = @pSpecValori_ID7 OR Filtru7 = @pSpecValori_ID7))

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-09 : 01:14:42

Looks like you need to normalize your database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

cda2007
Starting Member

17 Posts

Posted - 2010-03-09 : 03:49:48

It is normalised. The problem is that I have a table with a unique key formed from 3 columns(product_id, characteristic_name, characteristic_value) but only a few of the values are filters(on the asp page). I decide at runtime what the filters are.
HOW CAN I MAKE THIS WORK BETTER?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 04:36:43

well I generally hate to recommend this but:

You could:

1) Ditch the @produseFiltre table. At the moment you are effectively doing a SELECT on a heap with a very complex WHERE clause.

2) Build the SELECT query dynamically.

This way you wouldn't have to have such a complex WHERE clause (if a variable was NULL) then you just wouldn't add the relevant condition. Also because each permutation of the query would have it's own cached execution plan (there would be a different plan for each combination of NULL / NOT NULL parameters) then the changes of you using a good plan are higher.

As always, here's a great link for dynamic sql.
http://www.sommarskog.se/dynamic_sql.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-09 : 04:39:10

Here's a good link explaining why your query (and general purpose queries in... general) are fairly bad performers.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-03-09 : 11:53:36

I am waiting for response from other to learn how they solve your problem without database/table redesign. But look like you have no choice if you want to run the search faster.

The table @ProduseFiltre has 8 columns and 7 of them are used in the complex where clause for search(with a range). As you know, the good way for seach is creating an index, but it is not proper to creat 7 indexes on a table having only 8 columns....

If you find out the solution, please post it up.

cda2007
Starting Member

17 Posts

Posted - 2010-03-10 : 12:36:51

The dynamic sql link realy helped me a lot. I ran in a bit of a trouble.
I want to filter in the WHERE clause the rows to be writen.

Example:
DECLARE @ProduseFiltre table
(ProdusID INT,
Filtru1 INT,
Filtru2 INT,
Filtru3 INT,
Filtru4 INT,
Filtru5 INT,
Filtru6 INT,
Filtru7 INT)

Insert into @ProduseFiltre
SELECT Produs_ID as pd,
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID1 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID2 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID3 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID4 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID5 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID6 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID7 THEN pSpecValori_ID END)

FROM Produs_Specificatii
WHERE ( the result of the first MAX() ) = @parameter
GROUP BY Produs_ID

I tried "AS" after max but it does not recognizes my column.
Please help me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 12:44:35

may be this


SELECT Produs_ID as pd, 
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID1 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID2 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID3 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID4 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID5 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID6 THEN pSpecValori_ID END),
MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID7 THEN pSpecValori_ID END)

FROM Produs_Specificatii
GROUP BY Produs_ID
HAVING MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID1 THEN pSpecValori_ID END)=@parameter

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cda2007
Starting Member

17 Posts

Posted - 2010-03-10 : 14:10:04

Thank you so very much.
This is the working code and if anyone has an idea on how to improve it please, please don't be shy.

ALTER Procedure [dbo].[getProduseFiltre]
(@Ca_ID int = NULL,
@NrPag int = NULL,
@ProdusePePagina int = NULL,
@NrTotalProduse int = NULL OUTPUT,
@PretMinim decimal(9,2) = NULL,
@PretMaxim decimal(9,2) = NULL,

@pSpecNume_ID1 int = NULL,
@pSpecNume_ID2 int = NULL,
@pSpecNume_ID3 int = NULL,
@pSpecNume_ID4 int = NULL,
@pSpecNume_ID5 int = NULL,

@pSpecValori_ID1 int = NULL,
@pSpecValori_ID2 int = NULL,
@pSpecValori_ID3 int = NULL,
@pSpecValori_ID4 int = NULL,
@pSpecValori_ID5 int = NULL)
AS

----declare the sql to be executed
DECLARE @sql nvarchar(4000)

SELECT @sql = '
----declare table
DECLARE @ProduseFiltre table
(RowNumber INT,
Produs_ID INT,
Filtru1 INT,
Filtru2 INT,
Filtru3 INT,
Filtru4 INT,
Filtru5 INT,
PM_ID INT,
PM_Nume varchar(150),
Model varchar(150),
Pret decimal(9,2),
Imagine varchar(100),
PM_Imagine varchar(100),
Stoc int,
Ca_ID int)

Insert into @ProduseFiltre
SELECT ROW_NUMBER() OVER (ORDER BY ps.Produs_ID) AS Row,
ps.Produs_ID,
MAX(CASE WHEN ps.pSpecNume_ID = @pSpecNume_ID1 THEN pSpecValori_ID END) ,
MAX(CASE WHEN ps.pSpecNume_ID = @pSpecNume_ID2 THEN pSpecValori_ID END) AS Filtru2 ,
MAX(CASE WHEN ps.pSpecNume_ID = @pSpecNume_ID3 THEN pSpecValori_ID END) AS Filtru3 ,
MAX(CASE WHEN ps.pSpecNume_ID = @pSpecNume_ID4 THEN pSpecValori_ID END) AS Filtru4 ,
MAX(CASE WHEN ps.pSpecNume_ID = @pSpecNume_ID5 THEN pSpecValori_ID END) AS Filtru5 ,
pm.PM_ID,
pm.PM_Nume,
p.Model,
p.Pret,
p.Imagine,
pm.PM_Imagine,
p.Stoc,
c_pm.Ca_ID

FROM dbo.Produse p INNER JOIN
dbo.Produs_Specificatii ps ON p.Produs_ID = ps.Produs_ID INNER JOIN
dbo.ProdusMare pm ON p.Produs_Parent_ID = pm.PM_ID INNER JOIN
dbo.Categorii_ProdusMare c_pm ON pm.PM_ID = c_pm.PM_ID
WHERE c_pm.Ca_ID = @Ca_ID '

IF @PretMinim IS NOT NULL
SELECT @sql = @sql + ' AND p.Pret >= @PretMinim '

IF @PretMaxim IS NOT NULL
SELECT @sql = @sql + ' AND p.Pret <= @PretMaxim '

IF 1 =1
SELECT @sql = @sql + ' GROUP BY ps.Produs_ID, pm.PM_Nume, p.Model, p.Pret, p.Imagine, pm.PM_Imagine, p.Stoc, pm.PM_ID, c_pm.Ca_ID '

IF @pSpecValori_ID1 IS NOT NULL
SELECT @sql = @sql + ' HAVING MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID1 THEN pSpecValori_ID END) = @pSpecValori_ID1 '

IF @pSpecValori_ID2 IS NOT NULL
SELECT @sql = @sql + ' AND MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID2 THEN pSpecValori_ID END) = @pSpecValori_ID2 '

IF @pSpecValori_ID3 IS NOT NULL
SELECT @sql = @sql + ' AND MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID2 THEN pSpecValori_ID END) = @pSpecValori_ID3 '

IF @pSpecValori_ID4 IS NOT NULL
SELECT @sql = @sql + ' AND MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID2 THEN pSpecValori_ID END) = @pSpecValori_ID4 '

IF @pSpecValori_ID5 IS NOT NULL
SELECT @sql = @sql + ' AND MAX(CASE WHEN pSpecNume_ID = @pSpecNume_ID2 THEN pSpecValori_ID END) = @pSpecValori_ID5 '

IF 1 = 1
SELECT @sql = @sql + ' -- return the total number of products using an OUTPUT variable
SELECT @NrTotalProduse = COUNT(*) FROM @ProduseFiltre '

IF 1 = 1
SELECT @sql = @sql + ' SELECT RowNumber, Produs_ID, PM_ID, PM_Nume, Model, Pret, Imagine, PM_Imagine, Stoc, Filtru1, Filtru2, Filtru3, Filtru4, Filtru5' +
' FROM @ProduseFiltre '

EXEC sp_executesql @sql, N'@Ca_ID int,
@NrPag int,
@ProdusePePagina int,
@NrTotalProduse int OUTPUT,
@PretMinim decimal(9,2),
@PretMaxim decimal(9,2),

@pSpecNume_ID1 int,
@pSpecNume_ID2 int,
@pSpecNume_ID3 int,
@pSpecNume_ID4 int,
@pSpecNume_ID5 int,

@pSpecValori_ID1 int,
@pSpecValori_ID2 int,
@pSpecValori_ID3 int,
@pSpecValori_ID4 int,
@pSpecValori_ID5 int',
@Ca_ID,
@NrPag,
@ProdusePePagina,
@NrTotalProduse OUTPUT,
@PretMinim,
@PretMaxim,

@pSpecNume_ID1,
@pSpecNume_ID2,
@pSpecNume_ID3,
@pSpecNume_ID4,
@pSpecNume_ID5,

@pSpecValori_ID1,
@pSpecValori_ID2,
@pSpecValori_ID3,
@pSpecValori_ID4,
@pSpecValori_ID5

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources