How can I select middle .. From? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

How can I select middle .. From?

Author

Topic

newprog
Starting Member

5 Posts

Posted - 2007-10-25 : 03:40:20

I have a table Customer with 200000 records.

When I run query

DECLARE @posTable TABLE(idkey INT IDENTITY(1,1), value VARCHAR(128))
INSERT INTO @posTable (value) SELECT CustID FROM Customer ORDER BY CustID

At the first time it takes 1 minute 42 seconds, at the second time it only takes 3 seconds.

When i run the query

SELECT IDENTITY(INT, 1, 1) AS idkey, CustID INTO #posTable FROM Customer ORDER BY CustID
The order in column idkey is not exacts

Pls. tell me why? and How can i rewrite it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 03:44:04

Can you reproduce the problem with, say 20 customers?

E 12°55'05.25"
N 56°04'39.16"

newprog
Starting Member

5 Posts

Posted - 2007-10-25 : 03:53:16

With 20 customers it only takes less than 1 second in two cases.

Thanks for your quick reply.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 03:57:03

Thanks.
Do you still have the problem with idkey with only 20 customers?

E 12°55'05.25"
N 56°04'39.16"

newprog
Starting Member

5 Posts

Posted - 2007-10-25 : 04:02:57

With only 20 customers, It's No Problem.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 04:17:38

When do it become a problem?
Try again with 2000 customers. Then 5000, 10000, 20000 and so on...

E 12°55'05.25"
N 56°04'39.16"

newprog
Starting Member

5 Posts

Posted - 2007-10-25 : 04:47:52

When I try with 200, 500, 1000, 5000, 10000... Customers
Results:

Records Run at Fisrt Time (Senconds) Run at Sencond Time (Senconds)
200 0 0
500 1 0
1000 3 0
5000 7 1
10000 16 2
20000 20 2
50000 47 2
100000 65 3

I dont Know Why, but the Proplem has occurs when run query at the fist time .

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 05:13:27

Are you sure you included the ORDER BY statement then?

E 12°55'05.25"
N 56°04'39.16"

newprog
Starting Member

5 Posts

Posted - 2007-10-25 : 05:21:27

Yes, I'm sure

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources