May 2, 2012 at 10:59 pm
I making a string builder of thousands of update query.
At last I execute those queries in chunks(say 100 at a time).
In each update query almost 70 to 80 records are getting updated.
So I have thousands of such queries and want to execute them faster on sql server.
As of now it is taking 4 min to execute 100 update queries.
my update query is:-
UPDATE DRM SET [RunningBoard] = '" + RunningBoard + "',[DutyBoard] ='" + DutyBoard + "' ,[DriverId] ='" + DriverId + "' ,[PublicServiceCode] = '" + PublicServiceCode + "' ,[ServiceCode] = '" + ServiceCode + "',[DepotCode] = '" + DepotCode + "' ,[Direction] = " + Direction + ",[EOJMethod] = " + EOJMethod + " , JourneyNo=" + JourneyNo + " FROM DimRequestMasterTestHS DRM JOIN ETMMessageTestHS EM ON EM.ETMMessageTestHSID= DRM.ETMMessageID JOIN MessageWrapperTestHS1 MW ON EM.WrapperID= MW.ID WHERE MW.LiveETMSessionID = " + ETMLivesessionID + " and ((case when MW.MessageTimeStamp like '%00/00/00%' or MW.MessageTimeStamp like '%03/01/01%' then '01/01/9999' else CONVERT(datetime, MW.MessageTimeStamp,2) end) between CONVERT(datetime, '" + JourneyStart + "',101) and CONVERT(datetime,'" + JourneyEnd + "',101))
here I am passing the parameter values in query.
Such thousands of query I am making and I want to execute it faster.
May 3, 2012 at 12:15 am
1) please post some ddl of all involved objects and their indexes
2) please post an execution plan
3) I guess your MessageTimeStamp column has been declare char(23) and it contains a formatted kind of date variant which includes invalid dates ( 00/00/00 ) and you don't know where the heck the date part itself is supposed to be ( hence the '%00/00/00%' )
4) because of the need to use CONVERT(datetime, MW.MessageTimeStamp, 2) a possible index on that column will not be used in the most optimal way, if used at all.
Regarding those dates: that is one of the most common design faults we get to see. Date/time/datetime should be stored in columns declared with the proper data type !
You generate this kind of queries, based on another query, right ?
Why don't you just extend the update to join your "generation" query and launch it all at once, provided you optimize the query/indexes/...
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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply