Indexing question - 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)

Indexing question

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-09-14 : 05:06:54

I've got this query


SELECT 
ISNULL((select TOP 1 price FROM tblPricing WHERE product = 'CH' AND source = 'F1' and capid = 10 and maintained=1 and MilesPA=10000 and Term =3 AND type ='car'),9999) as F1,
ISNULL((select TOP 1 price FROM tblPricing WHERE product = 'CH' AND source = 'F2' and capid = 10 and maintained=1 and MilesPA=10000 and Term =3 AND type ='car'),9999) as F2,
ISNULL((select TOP 1 price FROM tblPricing WHERE product = 'CH' AND source = 'F3' and capid = 10 and maintained=1 and MilesPA=10000 and Term =3 AND type ='car'),9999) as F3

What index(s) should I be creating on tblPricing to enable this query to run optimally?

Thanks

Kristen
Test

22859 Posts

Posted - 2010-09-14 : 05:47:06

There has to be a more efficient way to do this. Pivot?

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2010-09-14 : 06:39:32

If there is I could sure use it. I'm iterating over this query nearly 100,000 times at the moment with different values. As you can imagine it's taking some time.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-09-14 : 11:33:23

quote:

SELECT
ISNULL((select TOP 1 price FROM tblPricing WHERE product = 'CH' AND source = 'F1' and capid = 10 and maintained=1 and MilesPA=10000 and Term =3 AND type ='car'),9999) as F1,
ISNULL((select TOP 1 price FROM tblPricing WHERE product = 'CH' AND source = 'F2' and capid = 10 and maintained=1 and MilesPA=10000 and Term =3 AND type ='car'),9999) as F2,
ISNULL((select TOP 1 price FROM tblPricing WHERE product = 'CH' AND source = 'F3' and capid = 10 and maintained=1 and MilesPA=10000 and Term =3 AND type ='car'),9999) as F3

Before talking about performance, I think you have to make sure your output is correct as you expected. Something is unclear/unnecessary that may affect using pivot or other ways in performance....

-Top 1 price is the largest price or the price which inserted first/last or something else? The above query may return different result as you expect.
-Isnull is really necessary?

If you can, explain more detail, or/and provide sample table with data and output you expect.

Kristen
Test

22859 Posts

Posted - 2010-09-14 : 11:38:31

Good point, using SELECT TOP without ORDER BY if going to give random results!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-14 : 11:59:25

unpivoting this and refactoring a little gives (maybe) (I don't know what your TOP 1 statements are trying to do) this


SELECT
	[source] AS [source]
	, MAX([price]) AS [price]
FROM
	tblPricing
WHERE
	[product] = 'CH'
	AND [capId] = 19
	AND [maintained] = 1
	AND [milesPA] = 10000
	AND [term] = 3
	AND [type] = 'car'
	AND [source] IN ('F1', 'F2', 'F3')
GROUP BY
        [source]

I think you should give some sample data and required results. I think what you have now is pretty bad -- whenever you start repeating code like that you should stop and think -- why.......

multiple subqueries is almost always a bad plan.

you'll get some helpful suggestions if you post the sample data and required output.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources