Need Guidelines For Using SELECT INTO
-
We have analysts who like to use SELECT INTO to create large tables for their reports. It has reached a critical mass with the number of blocking issues which have resulted. Does anyone have a formulated guideline or strategy for controlling this behavior? I do not want to turn off SELECT INTO/BULK COPY on the database if there is another solution.
-
Why can't he use view? What blocking issues did you notify?
-
Why do they even do select into??? Can't a straight select be used to present the report?
Assuming that it can't be used, I'd strongly suggest creating a permanent reporting table, or at least use a Table variable. Those solution will both resolve the blocking issues.
-
Select into in the wrong hands can make more bad than good!
If they do that,
Will they be able to create indexes on those tables also?
Will they be able to select the right columns for that?
Will the be able to compare several strategies and choose the one that affect others less or the one that has better performance?
I would think that most of the time the answer to all those questions is negative.
If the insist you can show them this trick
select ...
into #T1
from Source
where 1 = 1
and then
insert into #T1 select .....
That Way select into time and Lock are minimized but the performance will go downhill is the number of records affected are high.
your call ...
* Noel -
select ...
into #T1
from Source
where 1 = 0
--this forces to create the table only without inserting any rows, therefore taking virtually no time and releasing the locks immediatly.
-
Oops! I meant 1 = 0. I have to be more careful
* Noel -
Tag team strikes again
. -
Large tables is relative. How do you define "large table"?
Like Allen, I would be interested in knowing what blocking issues have been observed. And I would also be interested in the statements those analysts want to use. Will this be stored procedures? Do they *really* need the most recent data or might they be able to do their analysis with previous day's data? Hm, coming to think of it, there are a lot of questions to be answered before even thinking about how to technically solve them.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Sometimes it's just not worth upsetting the business analysts and supply them with a database which could be updated daily that they can do virtually what they want to and your main transaction database is seperated and treated with the high regard that it deserves.
That way they cause there own problems and your users aren't effected...
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply