| Author |
Topic |
|
Nithin
Starting Member
6 Posts |
 Posted - 2005-10-19 : 17:51:42
|
| Hi, I am having a query as follows:SELECT DISTINCT f1, f2, f3, f4 AS actdatetime --format = mmddyy8.INTO table1FROM table2 WHERE f1 >= @Startdate AND f1 <= @Enddate AND code IN ('NC','NB','NV','NS','TI')ORDER BY f1, f2It is a long-running query that uses "SELECT INTO".Is there any other way of doing it to increase the performance? Appreciate your help.Thanks,N |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-19 : 17:54:59
|
| Get rid of the ORDER BY. Once you use Table2, you can either use an ORDER BY there or you can throw a clustered index across f1 and f2.Tara |
 |
|
|
Nithin
Starting Member
6 Posts |
Posted - 2005-10-21 : 15:09:10
|
| Is there any way that I can avoid SELECT INTO statement? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-21 : 15:11:11
|
| It depends what your requirement is. Why are you using SELECT INTO in the first place?Tara |
|
|
|
Nithin
Starting Member
6 Posts |
Posted - 2005-10-21 : 15:26:46
|
| This query is existing already. I have created indexes on f1,f2. It's taking less time now. But I was asked to avoid SELECT INTO satement. |
|
|
|
anuj164
Starting Member
49 Posts |
Posted - 2005-10-21 : 15:34:36
|
| any reason why you were asked to avoid Select Into; as personally i don't think that's an issue. |
|
|
|
Nithin
Starting Member
6 Posts |
Posted - 2005-10-21 : 15:38:56
|
| Reason is that the query is taking more time. After creating the indexes it's taking less time. I think putting indexes solves the problem and there is no need to avoid SELECT INTO statement.Thanks for your valuable suggestions. Thanks,N |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-21 : 15:59:20
|
| SELECT INTO is used to create a new table. So does the application require this?Tara |
|
|
|
Nithin
Starting Member
6 Posts |
Posted - 2005-10-21 : 16:02:23
|
| Yes, the application requires it.Thanks,N |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-10-21 : 16:08:05
|
| Well then you can't remove it. The only thing you can do is create the table ahead of time, then insert into select * from... into that table.Tara |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-10-21 : 19:51:08
|
| It is always best to avoid a SELECT INTO. If the query runs a long time, it will hold a schema lock, and can block other people using the database.I see no reason why you couldn't use a CREATE TABLE statement and then an INSERT statement.What do you mean by the "application requires it"?CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-21 : 20:05:49
|
| "It is a long-running query that uses "SELECT INTO""How many rows in the original table?How many rows in the bit that you are selecting?I'm in the "create the table first" camp! Mainly because I expect you will want to make a clustered PK on f1, f2 on the new table. And I would like to be sure that any varchar columns got a prescribed collation - but that's probably just me being pedantic!"Get rid of the ORDER BY. Once you use Table2, you can either use an ORDER BY there or you can throw a clustered index across f1 and f2"I haven't tried it, but does the Clustered Index create more quickly if the data in the table was already in the right order? Or is that only the case if you BCP in data [i.e. pre-ordered] into a table with a pre-existing clustered index?Kristen |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-10-22 : 04:10:56
|
>> I haven't tried it, but does the Clustered Index create more quickly if the data in the table was already in the right order? Or is that only the case if you BCP in data [i.e. pre-ordered] into a table with a pre-existing clustered index?I don't know about performance, but the fragmentation of the index is much lower if you use appropriate order by. I did compare once.If I have a clustered index, I order by that when doing insert into <empty> table... mostly... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-22 : 05:32:24
|
| "doing insert into <empty> table... "I must admit that would seem the obvious choice to me:Create tableCreate clustered index (or PK)INSERT INTO table SELECT * FROM OtherSource(s) ORDER BY PrimaryKeyColumn(s)then Create Other IndexesI expect BCP IN using-h "ORDER (PrimaryKeyColumn(s))"is faster, but I never seem to have external data to import like that ... its always in Staging Tables by that time. Perhaps I should BCP it OUT and back IN rather than INSERT INTO ... ORDER BY with all the logging and sorting effort that that implies. I suppose I should have a clustered index on the staging table too so that the ORDER BY is not required.Kristen |
|
|
|
Nithin
Starting Member
6 Posts |
Posted - 2005-10-24 : 10:58:04
|
| "It is always best to avoid a SELECT INTO. If the query runs a long time, it will hold a schema lock, and can block other people using the database.I see no reason why you couldn't use a CREATE TABLE statement and then an INSERT statement.What do you mean by the "application requires it"?CODO ERGO SUM"I think I need to use a CREATE TABLE and then an INSERT statment. Because it is blocking other people using the database.Thanks,N |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-10-24 : 15:03:42
|
| "I think I need to use a CREATE TABLE and then an INSERT statment"IMHO its all to easy to doSELECT * INTO NewTable FROM OldTablewhen actually 99,999 out of 100,000 it would be way better to create the blasted NewTable first!Kristen |
|
|
|
|
Performance issue