April 18, 2012 at 1:29 am
The select query takes about a min to retrieve 7200 records.Need it to be quick say around 3 seconds
The table definition is as follows
create TABLE TMPPACKAGEOPTION
(
ID INT IDENTITY(1,1) PRIMARY KEY,
SPID int,
PACKAGEID INT,
PACKAGEDEPARTUREID INT,
PACKAGEELEMENTID INT,
PACKAGEOPTIONID INT,
SERVICETYPEOPTIONNAME VARCHAR(100),
SERVICETYPETYPENAME VARCHAR(50),
OCCUPANCYTYPENAME VARCHAR(50),
SERVICEID INT,
SERVICENAME VARCHAR(500),
SERVICETYPENAME VARCHAR(50),
SERVICEDESCRIPTION VARCHAR(2048),
SERVICESTARRATING VARCHAR(50),
ELEMENT_NAME VARCHAR(50),
CHILDCAPACITY INT,
REGIONSHORTNAME VARCHAR(50),
REGIONNAME VARCHAR(50),
REGIONID INT,
PACKAGESERVICEID INT
,CHECKINTIME DATETIME,
CHECKOUTTIME DATETIME
,FIXEDDEPARTURE BIT DEFAULT 0
)
The problem select statement is as follows
SELECT id,
PACKAGEID ,
PACKAGEDEPARTUREID ,
PACKAGEELEMENTID ,
PACKAGEOPTIONID ,
SERVICETYPEOPTIONNAME ,
SERVICETYPETYPENAME ,
OCCUPANCYTYPENAME ,
SERVICEID ,
SERVICENAME ,
SERVICETYPENAME ,
SERVICEDESCRIPTION ,
SERVICESTARRATING ,
ELEMENT_NAME ,
CHILDCAPACITY ,
REGIONSHORTNAME ,
REGIONNAME ,
REGIONID ,
PACKAGESERVICEID
,CHECKINTIME
,CHECKOUTTIME
FROM TMPPACKAGEOPTION
where packageid=891
using the database tuning advisor i used the following recommendations
CREATE NONCLUSTERED INDEX [_dta_index_TMPPACKAGEOPTION_Opti] ON [dbo].[TMPPACKAGEOPTION]
(
[PACKAGEID] ASC
)
INCLUDE ( [ID],
[PACKAGEDEPARTUREID],
[PACKAGEELEMENTID],
[PACKAGEOPTIONID],
[SERVICETYPEOPTIONNAME],
[SERVICETYPETYPENAME],
[OCCUPANCYTYPENAME],
[SERVICEID],
[SERVICENAME],
[SERVICETYPENAME],
[SERVICEDESCRIPTION],
[SERVICESTARRATING],
[ELEMENT_NAME],
[CHILDCAPACITY],
[REGIONSHORTNAME],
[REGIONNAME],
[REGIONID],
[PACKAGESERVICEID],
[CHECKINTIME],
[CHECKOUTTIME]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
But this did not help as actually i require all the records in the table
Have attached a screenshot of the actual execution plan...
Any help would be appreciated... As of now am using a top 300 and using a loop so that i avoid a timeout from the application.
Have also attached the actual execution plan with the.sqlplan extension
April 18, 2012 at 1:55 am
That query is as optimal as it is going to get (though the index is horrid and should likely never have been created)
If it's slow check whether the problem is getting the data off disk (so faster disks needed) or getting it to the client (so faster network/client needed)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2012 at 3:03 am
apparently someone applied DataTuningAdvisor indexes, which is BAD if applied blindly. _dta_index_TMPPACKAGEOPTION_Opti
IMO, because your query selects directly on the PK column, which is defined as clustering ix ( sql server will make a pk index clustered if the object doesn't already have a clustering index ) this DTA index is pure overhead in this case.
Your query should perform well enough to accomplish the requested within your desired time frame, provided your instance doesn't have to upload to much data in memory.
( show statistics io on will provide more info so you can check if it does physical IO)
Keep in mind it has to process 11MB to fetch your (single) row (1.582 bytes) !
What else is running in your instance ( internal pressure ) and on your hosting box ( external pressure ) ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 18, 2012 at 3:20 am
ALZDBA (4/18/2012)
IMO, because your query selects directly on the PK column, which is defined as clustering ix ( sql server will make a pk index clustered if the object doesn't already have a clustering index )
It's not filtering on the pk. The primary key is ID, the filter is on packageid.
Keep in mind it has to process 11MB to fetch your (single) row (1.582 bytes) !
It's not returning a single row. Its returning 7200.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2012 at 3:21 am
Thanks, have checked that....was accessing the database server over the network...tried executing the same query from the database server and it was quick 4 sec the first run and zero on the next...Thanks again i really appreciate it.
April 18, 2012 at 3:25 am
Then your problem is with the network latency. Speak to a network admin, the database is not the problem here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 18, 2012 at 4:04 am
GilaMonster (4/18/2012)
ALZDBA (4/18/2012)
IMO, because your query selects directly on the PK column, which is defined as clustering ix ( sql server will make a pk index clustered if the object doesn't already have a clustering index )It's not filtering on the pk. The primary key is ID, the filter is on packageid.
Keep in mind it has to process 11MB to fetch your (single) row (1.582 bytes) !
It's not returning a single row. Its returning 7200.
How could I miss that :blush::blush::blush::blush:
Of course that is the reason why it hands over 11MB of data.
Very, very sorry for that :blush:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply