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.
| Author |
Topic |
|
sanghavi17389
Starting Member
26 Posts |
 Posted - 2012-05-02 : 22:28:49
|
| 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.harshal sanghavi |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-05-03 : 12:43:56
|
| Is this a batch job or part of transaction workflow?A number of factors determine how quickly UPDATES occur. One of them is how many Non-Clustered Indexes need to be updated. Supply a DDL of the targe tableJack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-05-03 : 13:02:33
|
| You are using a non-sargable predicate (LIKE '%...'). So, the update can't realy make use of an indexe (at least for that column). |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-03 : 14:37:07
|
| why are you using string comparison for date values? why cant you use comparison operators like >=,< for them? Are the native field data types datetime or date?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sanghavi17389
Starting Member
26 Posts |
Posted - 2012-05-04 : 00:56:11
|
| my MessageTimestamp field is varchar and in that I am getting value like 12/03/25. Moreover, sometimes invalid records like 00/00/00 00:00:00 and 03/01/01 00:00:00 are dumped in this field.I have to ignore these kind of invalid data.JourneyStart and JourneyEnd fields are datetime.I have to check that MessageTimeStamp falls between JourneyStart and Journeyend.harshal sanghavi |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-04 : 18:34:12
|
quote:
Originally posted by sanghavi17389
my MessageTimestamp field is varchar and in that I am getting value like 12/03/25. Moreover, sometimes invalid records like 00/00/00 00:00:00 and 03/01/01 00:00:00 are dumped in this field.I have to ignore these kind of invalid data.JourneyStart and JourneyEnd fields are datetime.I have to check that MessageTimeStamp falls between JourneyStart and Journeyend.harshal sanghavi
then you've to use convertions to make them date values before you do comparison------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Update query Perfomance