Slow SQL Query

Question

Slow SQL Query

Viewing 5 posts - 46 through 49 (of 49 total)

You must be logged in to reply to this topic. Login to reply

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 1

drop it into a text file and attach the text file.

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

maxyogesh2002 Ten Centuries Points: 1062 More actions · Answer 2

here i m attached the table defination...

plz check & tell me is anything need to check.

revert me back ASAP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Dave Ballantyne (7/13/2009)
Select into will be faster if 'select into/bulkcopy' is set in sp_dbOption as it will be a non-logged operation.

From Books Online:

Starting with Microsoft SQL Server 2000, if the recovery model of the database is currently set to FULL, using the select into/bulkcopy option resets the recovery model to BULK_LOGGED. The proper way to change the recovery model is by using the SET RECOVERY clause of the ALTER DATABASE statement.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

maxyogesh2002 Ten Centuries Points: 1062 More actions · Answer 4

i optimize my SQL Query

select Distinct Auditdata.ID into Auditdata_sms FROM auditdata AuditData

INNER JOIN AuditMaster am ON am.ID = AuditData.AuditMaster_ID

iNNER JOIN HomeCircleMaster hcm ON hcm.Ori_CircleMaster_ID = am.CircleMaster_ID AND hcm.Ori_ServiceTypeMaster_ID = 1 AND hcm.Dest_ServiceTypeMaster_ID = 1

INNER JOIN AuditTaggingMaster atm ON atm.AuditMaster_ID = am.ID

INNER JOIN NoSeriesMaster ns on (ns.CircleMaster_ID = am.CircleMaster_ID or ns.CircleMaster_ID = hcm.Dest_CircleMaster_ID) AND ns.ProviderMaster_ID = am.ProviderMaster_ID AND ns.ServiceTypeMaster_ID = 1

INNER JOIN ProviderMaster_CallTypeMaster pm_ctm ON pm_ctm.ProviderMaster_ID = am.ProviderMaster_ID AND pm_ctm.CallTypeMaster_ID = 101 AND pm_ctm.CallTypeTagValue = AuditData.CallTypeTag

INNER JOIN NoSeriesMaster_Prefix PD ON SUBSTRING(AuditData.CallTo, 1, CONVERT(INT, PD.PrefixLen)) = PD.PrefixNo AND LEN(AuditData.CallTo) = CONVERT(VARCHAR(10), CONVERT(INT, PD.PrefixLen) + CONVERT(INT, PD.AfterPrefixLen)) AND PD.PrefixNo + ns.NoSeries = LEFT(AuditData.CallTo, len(ns.NoSeries) + CONVERT(INT, PD.PrefixLen))

WHERE AuditData.TATCallType is NULL AND AuditData.AuditMaster_ID = 74 AND PD.PrefixType = 'SMS'

now my above query takes 4 minutes to take against 1500000 rows

using above query i m taking Original table ID to the temp table its take around 4.00 minutes

but after that i need to update tatcalltype,tatcallunit to original table using ID columns for that i m using below query.

update Auditdata set tatcalltype=20,tatcallunit=1 from auditdata auditdata

inner join auditdata_sms a_sms on auditdata.id=a_sms.id

the above query takes more than 4 minutes whats wrong in above query even both table ID column datatype is integer

Please reply ASAP

tosscrosby SSCoach Points: 18045 More actions · Answer 5

maxyogesh2002 (7/13/2009)
revert me back ASAP

I don't think there is any "revert" happening here anytime soon.........

-- You can't be late until you show up.