September 2, 2004 at 1:58 pm
DECLARE @BackupLoc varchar(50)
DECLARE @SQLStr NVarchar(250)
DECLARE @Thistable varchar(50)
SET @BackupLoc = '<mydb>.dbo'
SET @Thistable = 'mytable' + Convert(char(8), Getdate(), 112)
SET @SQLstr = 'SELECT * INTO ' + @BackupLoc + @Thistable + ' from mytable'
exec sp_executesql @SQLstr
The above statement excutes within a stored procedure. If mytable is indexed, the statement executes within 2 minutes for over 2 million records.
If not indexed, the statement takes over 2 hours to complete. Is there a specific reason in the exec sp_executesql statement that accounts for this?
September 2, 2004 at 11:33 pm
Have you run this in QA and looked at the execution plan for both an indexed table and a non-indexed table?
That might point you in the right direction.
Have you tried running this as a stored procedure from QA showing the execution plan without using the exec, just actually typing the select * from dbname.dbo.tablename from mytable for both indexed and non-indexed tables?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply