December 4, 2007 at 8:01 am
I have a problem where my users complain that a select statement takes too long, at 90 seconds, to read 120 records out of a database.
The select statement reads from 9 tables three of which contain 1000000 records, the others contain between 100 and 250000 records.
I have checked that each column in the joins are indexed - they are (but some of them are clustered indexes, not unclustered).
I have run the SQL Profiler trace from the run of the query through the "Database Engine Tuning Advisor". That just suggested two statistics items which I added (no benefit) and two indexes for tables that are not involved at all in the query (I didn't add these).
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
So my question is: Is it normal for such a type of read query to take 90 seconds to complete?
Is there anything I could do to speed it up.
Any other thoughts?
Thanks
http://90.212.51.111 domain
December 4, 2007 at 9:10 am
Can you post the query and which columns are indexed on which tables?
Could be a valid run time, or could not. Hard to tell.
Look in the plan for table scans, If you're getting one on a large table, it could easily get to that time.
December 4, 2007 at 9:29 am
Seems awfully long - sounds like something's missing.
neil (12/4/2007)
I also ran the query through the Query window in SSMS with "Include Actual Execution Plan" enabled. This showed that all the execution time was being taken up by searches of the clustered indexes.
Search is not the right word - was it a seek or a scan? makes a BIG difference.
I have tried running the select with just three tables involved, and it completes fast. I added a fourth and it took 7 seconds. However there was no WHERE clause for the fourth table, so I got a cartesian product which might have explained the problem.
You really should move up to current ANSI notation as to JOINS, so that you don't have cartesian products going on... It really does help clear up those kind of issues IMO.
As Steve said though - post the query, so we can take a look.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 4, 2007 at 10:30 am
Here is my query. 9 tables.
They were seeks not scans.
select [D1].[SID_0COMPANY] AS [SID_0COMPANY]
FROM mdp.[/BIC/FZFIGL_C02] [F] JOIN mdp.[/BIC/DZFIGL_C02T] [DT] ON [F].[KEY_ZFIGL_C02T] = [DT].[DIMID]
JOIN mdp.[/BI0/SFISCYEAR] [S2] ON [DT].[SID_0FISCYEAR] = [S2].[SID]
JOIN mdp.[/BIC/DZFIGL_C022] [D2] ON [F].[KEY_ZFIGL_C022] = [D2].[DIMID]
JOIN mdp.[/BIC/DZFIGL_C023] [D3] ON [F].[KEY_ZFIGL_C023] = [D3].[DIMID]
JOIN mdp.[/BIC/DZFIGL_C02U] [DU] ON [F].[KEY_ZFIGL_C02U] = [DU].[DIMID]
JOIN mdp.[/BIC/DZFIGL_C024] [D4] ON [F].[KEY_ZFIGL_C024] = [D4].[DIMID]
JOIN mdp.[/BIC/DZFIGL_C02P] [DP] ON [F].[KEY_ZFIGL_C02P] = [DP].[DIMID]
JOIN mdp.[/BIC/DZFIGL_C021] [D1] ON [F].[KEY_ZFIGL_C021] = [D1].[DIMID]
where ( ( ( ( [DP].[SID_0CHNGID] = 0 ) )
AND ( ( [D1].[SID_0COMPANY] IN ( 21 , 66 , 34 , 36 , 37 , 35 , 33 , 38 , 39 , 112 , 121 ) ) )
AND ( ( [DT].[SID_0FISCPER3] = 12 ) )
AND ( ( [S2].[FISCYEAR] = N'2007' ) )
AND ( ( [DP].[SID_0RECORDTP] = 0 ) )
AND ( ( [DP].[SID_0REQUID] <= 2000000250 ) ) ) )
The following tables have the following indexes.
mdp.[/BIC/FZFIGL_C02]Clustered index on KEY_ZFIGL_C02P
nonclustered nonunique index on KEY_ZFIGL_C02T
nonclustered nonunique index on KEY_ZFIGL_C02U
nonclustered nonunique index on KEY_ZFIGL_C021
nonclustered nonunique index on KEY_ZFIGL_C022
nonclustered nonunique index on KEY_ZFIGL_C023
nonclustered nonunique index on KEY_ZFIGL_C024
nonclustered nonunique index on all the above columns
mdp.[/BIC/DZFIGL_C02T]nonclustered nonunique index on four columns
SID_0FISCPER
SID_0FISCVARNT
SID_0FISCPER3
SID_0FISCYEAR
clustered index on DIMID
mdp.[/BI0/SFISCYEAR]clustered index on two columns
FISCVARNT
FISCYEAR
nonclustered unique index on SID
mdp.[/BIC/DZFIGL_C022]nonclustered nonunique index on
SID_0CHRT_ACCTS
SID_0GL_ACCOUNT
SID_0AC_DOC_NO
SID_0ITEM_NUM
SID_0PSTNG_DATE
SID_0COORDER
SID_ZMWSKZ
SID_0CLEAR_DATE
SID_0BCS_INITEM
SID_0CS_ITEM
SID_0CS_CHART
clustered index on DIMID
mdp.[/BIC/DZFIGL_C023]nonclustered nonunique index on
SID_0AC_DOC_TYP
SID_0MOVE_TYPE
SID_0POST_KEY
clustered index on DIMID
mdp.[/BIC/DZFIGL_C02U]nonclustered nonunique index on
SID_0DOC_CURRCY
SID_0LOC_CURRC2
SID_0LOC_CURRC3
SID_0LOC_CURRCY
SID_0UNIT
clustered index on DIMID
mdp.[/BIC/DZFIGL_C024]nonclustered nonunique index on
SID_0WBS_ELEMT
SID_0PART_PRCTR
SID_0PCOMPANY
SID_0PBUS_AREA
SID_0LOC_CURTP2
SID_0LOC_CURTP3
SID_0REF_KEY1
SID_0REF_KEY2
clustered index on DIMID
mdp.[/BIC/DZFIGL_C02P]nonclustered nonunique index on
SID_0CHNGID
SID_0RECORDTP
SID_0REQUID
clustered index on DIMID
mdp.[/BIC/DZFIGL_C021]nonclustered nonunique index on
SID_0COMP_CODE
SID_0COSTCENTER
SID_0PROFIT_CTR
SID_0CO_AREA
SID_0COMPANY
clustered index on DIMID
The Actual query plan showed:
Index Seek 11%
6 * clustered index seek at 11%
3 * index seek also at 11%
http://90.212.51.111 domain
December 4, 2007 at 10:42 am
This may or may not resolve the data a little faster but helps me a little bit if you can get the text version of the execution plan and post it.
SELECT
[D1].[SID_0COMPANY] AS [SID_0COMPANY]
FROM
mdp.[/BIC/FZFIGL_C02] [F]
INNER JOIN
mdp.[/BIC/DZFIGL_C02T] [DT]
INNER JOIN
mdp.[/BI0/SFISCYEAR] [S2]
ON
[DT].[SID_0FISCYEAR] = [S2].[SID]
AND [S2].[FISCYEAR] = N'2007'
AND [DT].[SID_0FISCPER3] = 12
ON
[F].[KEY_ZFIGL_C02T] = [DT].[DIMID]
INNER JOIN
mdp.[/BIC/DZFIGL_C022] [D2]
ON
[F].[KEY_ZFIGL_C022] = [D2].[DIMID]
INNER JOIN
mdp.[/BIC/DZFIGL_C023] [D3]
ON
[F].[KEY_ZFIGL_C023] = [D3].[DIMID]
INNER JOIN
mdp.[/BIC/DZFIGL_C02U] [DU]
ON
[F].[KEY_ZFIGL_C02U] = [DU].[DIMID]
INNER JOIN
mdp.[/BIC/DZFIGL_C024] [D4]
ON
[F].[KEY_ZFIGL_C024] = [D4].[DIMID]
INNER JOIN
mdp.[/BIC/DZFIGL_C02P] [DP]
ON
[F].[KEY_ZFIGL_C02P] = [DP].[DIMID]
AND [DP].[SID_0CHNGID] = 0
AND [DP].[SID_0RECORDTP] = 0
AND [DP].[SID_0REQUID] <= 2000000250
INNER JOIN
mdp.[/BIC/DZFIGL_C021] [D1]
ON
[F].[KEY_ZFIGL_C021] = [D1].[DIMID]
AND [D1].[SID_0COMPANY] IN ( 21 , 66 , 34 , 36 , 37 , 35 , 33 , 38 , 39 , 112 , 121 )
December 4, 2007 at 10:52 am
...and based on Antares' reformat - you might care to add on extra index per table covering each of the fields in the ON statement, and INCLUDE anything in the select.
For example:
Create index ix_d1
on [D1].[SID_0COMPANY](DIMID,SID_OCOMPANY)
Create index ix_dT
on mdp.[/BIC/DZFIGL_C02T](DIMID,SID_0FISCYEAR,SID_0FISCPER3)
Create index ix_dP
on mdp.[/BIC/DZFIGL_C02P](DIMID,[SID_0CHNGID] ,[SID_0RECORDTP] ,SID_0REQUID])
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 4, 2007 at 10:54 am
One note
mdp.[/BIC/FZFIGL_C02] Clustered index on KEY_ZFIGL_C02P
nonclustered nonunique index on KEY_ZFIGL_C02T
nonclustered nonunique index on KEY_ZFIGL_C02U
nonclustered nonunique index on KEY_ZFIGL_C021
nonclustered nonunique index on KEY_ZFIGL_C022
nonclustered nonunique index on KEY_ZFIGL_C023
nonclustered nonunique index on KEY_ZFIGL_C024
nonclustered nonunique index on all the above columns
I would drop the last index for sure and possibly the other non-clustered indexes as well. Just a question, do you always have the above table as your anchor table in queries (meaning it is the primary focus to the other tables? If so then odds are those indexes are not helping becuase they will not be used in the join most times unless you say where that column from that table has a specific value. However that said the last index if choosen by the query engine may take longer in the joins becuase only the first column will control the sorrt of the index thus all other joins will have to seek their values if the egine joins from the other tables to this table. Consider dropping that one at least and see if the time doesn't improve.
December 4, 2007 at 11:01 am
Matt Miller (12/4/2007)
...and based on Antares' reformat - you might care to add on extra index per table covering each of the fields in the ON statement, and INCLUDE anything in the select.For example:
Create index ix_d1
on [D1].[SID_0COMPANY](DIMID,SID_OCOMPANY)
Create index ix_dT
on mdp.[/BIC/DZFIGL_C02T](DIMID,SID_0FISCYEAR,SID_0FISCPER3)
Create index ix_dP
on mdp.[/BIC/DZFIGL_C02P](DIMID,[SID_0CHNGID] ,[SID_0RECORDTP] ,SID_0REQUID])
I probably would remove before I considered adding. For example in the Matt suggestion
Create index ix_d1
on [D1].[SID_0COMPANY](DIMID,SID_OCOMPANY)
here is the table as described in your post
mdp.[/BIC/DZFIGL_C021] nonclustered nonunique index on
SID_0COMP_CODE
SID_0COSTCENTER
SID_0PROFIT_CTR
SID_0CO_AREA
SID_0COMPANY
clustered index on DIMID
Consider this fact DIMID is the clustered index and SID_0COMPANY is in a non-clustered index. Under the hood of indexes the clustered index value is always also in the non-clustered index, because of this the index for SID_0COMPANY is actually to the Server
SID_0COMPANY, DIMID
But I think the key for you ultimately will be indexing.
December 4, 2007 at 11:04 am
BTW another thought, is this true
[S2].[FISCYEAR] = N'2007'
Is FISCYEAR really an NVARCHAR column if not but is a VARCHAR do like so
[S2].[FISCYEAR] = '2007'
but if it is numeric do like so
[S2].[FISCYEAR] = 2007
If not numeric I might consider changing to smallint or other numeric type to reduce implicit conversions the system may be doing to manage comparison.
December 4, 2007 at 11:05 am
December 4, 2007 at 11:13 am
neil (12/4/2007)
Here is the execution plan I saved as a .sqlplan file:
Sorry I don't see it.
December 4, 2007 at 11:26 am
December 5, 2007 at 9:31 am
Looking at it things look good as all the index lookups are seeks. However, I would still review and try all of my recommendations
1) Try my altered query and see if the execution plan changes, I would hope to see the join between [/BIC/DZFIGL_C02T] [DT] AND mdp.[/BI0/SFISCYEAR] occur first.
2) Drop the index from
mdp.[/BIC/FZFIGL_C02] Clustered index on KEY_ZFIGL_C02P -- Keep this one for sure
nonclustered nonunique index on KEY_ZFIGL_C02T -- Keep this one
nonclustered nonunique index on KEY_ZFIGL_C02U <-
nonclustered nonunique index on KEY_ZFIGL_C021 | Depending on if there are other queries
nonclustered nonunique index on KEY_ZFIGL_C022 | run that would use these I would
nonclustered nonunique index on KEY_ZFIGL_C023 | probably drop these
nonclustered nonunique index on KEY_ZFIGL_C024 <-
nonclustered nonunique index on all the above columns --- Drop this index
3) Is FISCYEAR in mdp.[/BI0/SFISCYEAR] is a VARCHAR column do
[S2].[FISCYEAR] = '2007'
but if it is numeric do like so
[S2].[FISCYEAR] = 2007
4) You start in your join with 18 Mill rows then reduces to 1.5 million and then down further. The trick I would like to see is to get something less than 18 Mill in the first join step, doing so might drop the time it takes.
December 6, 2007 at 12:58 am
And what about replacing IN by CTE or temporary table?
for example:
WITH CTE_Code(Code) AS
(
SELECT 21 , 66 , 34 , 36 , 37 , 35 , 33 , 38 , 39 , 112 , 121
UNION ALL
SELECT 66
UNION ALL
SELECT 34
UNION ALL
SELECT 36
UNION ALL
SELECT 37
UNION ALL
SELECT 35
UNION ALL
SELECT 33
UNION ALL
SELECT 38
UNION ALL
SELECT 39
UNION ALL
SELECT 112
UNION ALL
SELECT 121
)
SELECT ... with joining D1 and CTE_Code?
December 6, 2007 at 12:59 am
Sorry for mistake, it should be:
WITH CTE_Code(Code) AS
(
SELECT 21
UNION ALL
SELECT 66
UNION ALL
SELECT 34
UNION ALL
SELECT 36
UNION ALL
SELECT 37
UNION ALL
SELECT 35
UNION ALL
SELECT 33
UNION ALL
SELECT 38
UNION ALL
SELECT 39
UNION ALL
SELECT 112
UNION ALL
SELECT 121
)
SELECT ... with joining D1 and CTE_Code
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply