October 29, 2010 at 5:22 am
hi
I am having a problem in SQL 2008 RTM the time to retun the query is much longer
Table structure is as below
Column_nameTypeComputedLengthPrecScaleNullable
Col1intno4100no
Col2varcharno20 no
Col3varcharno30 yes
Col4varcharno20 yes
Col5varcharno30 yes
Col6varcharno30 yes
Col7varcharno30 yes
Col8varcharno5 yes
Col9varcharno60 yes
Col10varcharno60 yes
Col11varcharno30 yes
Col12varcharno5 yes
Col13varcharno20 yes
Col14varcharno14 yes
Col15varcharno40 yes
Col16bigintno8 yes
Col17datetimeno8 yes
Col18varcharno1 yes
Col19varcharno1 yes
Col20varcharno1 yes
Col21varcharno1 yes
Col22moneyno8194yes
Col23datetimeno8 yes
Col24varcharno15 yes
Col25varcharno50 yes
Col26varcharno20 yes
Col27varcharno15 yes
Col28varcharno2 yes
Col29datetimeno8 yes
Col30datetimeno8 yes
Col31datetimeno8 yes
Col32varcharno1 yes
Col33datetimeno8 yes
Col34datetimeno8 yes
Col35intno4100yes
Col36datetimeno8 yes
Col37intno4100yes
Col38datetimeno8 yes
Col39varcharno-1 yes
Col40datetimeno8 yes
Col41varcharno20 yes
Col42datetimeno8 yes
Col43varcharno5 yes
Col44varcharno20 yes
Col45datetimeno8 yes
Col46varcharno70 yes
Col47moneyno8194yes
Col48moneyno8194yes
Col49moneyno8194yes
Col50moneyno8194yes
Col51varcharno50 yes
Col52varcharno50 yes
Col53datetimeno8 yes
Col54varcharno50 yes
Col55varcharno50 yes
Col56datetimeno8 yes
Col57varcharno20 yes
Col58datetimeno8 yes
Col59datetimeno8 yes
Col60moneyno8194yes
Col61datetimeno8 yes
Col62varcharno20 yes
Col63varcharno2 yes
Col64varcharno20 yes
Col65datetimeno8 yes
Col66intno4100yes
Col67varcharno20 yes
Sample column data
Select 36,'MNOPQRS','TESTING','XXX-XX-XXX','FNAME','LNAME','MNAME',NULL,'ADDRESS1','ADDRESS2','CITY','LP',98765,'XXX-XXX-XXXX',NULL,NULL,'1900-10-11 00:00:00.000','F','M',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'MNOPQRS',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2010-10-28 00:00:00.000',NULL,NULL,NULL,NULL,NULL,'All',NULL,NULL,NULL
Here Null fileds will be there because i got the file from currect client as null,but some client will give the values filled(not fully but some time they may)
My problem is i am using :
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
3 GB RAM
1 CPU
As i have given the sample data in my table i am having same type of data of nearly 10,000 records. what happening is when i select on the table table is taking more than 2 min to return the data this is fresh table so no one is accessing except me and only 15-20 tables are there in my db.
It is fresh db in developement.In the server there is another db with 1.5GB .it will have about 10-20 connections for 1 hour.
how to resolve this
i have created table on the data the client has given to me.
one option is col1 and col2 can be created as unique clustered index i tried that too but but same result it rduce to 1 min 45 sec but there is no other option to reduce this or why it is taking 1.45 min to return only for 10,000 records any idea or Suggestions :hehe: :hehe: :hehe:
Thanks
Parthi
Thanks
Parthi
October 29, 2010 at 6:19 am
Much longer than what? Another server? Compare the actual execution plans.
Do you get different plans? Compare indexes and statistics.
Which query? Can you post it? Can you post the actual execution plan from both environments?
-- Gianluca Sartori
October 29, 2010 at 6:59 am
You're really naming all your columns, Col1, col2... Troubleshooting and developing against that should be a lot of fun.
What kind of query are you trying to run? You don't list any indexes, keys or constraints with the table. If you're simply running queries on a heap (a table without a clustered index) and no other indexes, all you can expect is a table scan.
To really drill down, you have to show what query you're running, and an execution plan. For detailed help, actual DDL and DML scripts that allow us to replicate what you have would be useful too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2010 at 7:17 am
Much longer than what? Another server? Compare the actual execution plans.
I meaned here is, will it take nearly 2 min to return 10,000 records,i am not having any other server to cross verify
Do you get different plans? Compare indexes and statistics.NO same execution plan i am getting
Which query? Can you post it? Can you post the actual execution plan from both environments?
the table structure is what i have posted i am just querying select of the table (Select * from MyTable_name),the attached is the execution plan
Thanks
Parthi
Thanks
Parthi
October 29, 2010 at 7:23 am
SELECT * FROM Table
This query will simply scan the table. So, performance is strictly up to the hardware. How fast are your disks? How fast is your CPU? That's the only metrics you can use for this type of thing. If you're looking to enhance performance, buy bigger hardware. What exactly is this query & table in support of? What are you trying to do?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2010 at 7:24 am
Grant Fritchey (10/29/2010)
You're really naming all your columns, Col1, col2... Troubleshooting and developing against that should be a lot of fun.
The Column name i have kept on my server is correct naming but to hidce my business logic i have renamed and posted here as column 1 ....so nothing wrong in it
Thanks
Parthi
Thanks
Parthi
October 29, 2010 at 7:32 am
parthi-1705 (10/29/2010)
Grant Fritchey (10/29/2010)
You're really naming all your columns, Col1, col2... Troubleshooting and developing against that should be a lot of fun.The Column name i have kept on my server is correct naming but to hidce my business logic i have renamed and posted here as column 1 ....so nothing wrong in it
Thanks
Parthi
OK, fine, but 65 nullable columns in a table, you're running a SELECT * without a where clause or filters, and you have problem in SQL Server 2008? What's the problem? I'm just confused by what issue you're trying to define here. It's not making any sense.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2010 at 7:37 am
Am I correct that this table does not have a clustered index nor a primary key?
That seems to be what you're describing.
If so, that's probably a major part of your problem.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 29, 2010 at 7:40 am
GSquared (10/29/2010)
Am I correct that this table does not have a clustered index nor a primary key?That seems to be what you're describing.
If so, that's probably a major part of your problem.
Actually, based on the picture of the property sheets from an execution plan, it does have a clustered index. It's just doing a scan of it, as you would expect with a query that doesn't have a WHERE clause. It just hasn't been defined here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2010 at 7:41 am
No i am not saying problem on sql server i am asking how we can reduce the query time to less than 1 min for 10,000 records i have given the table structure and sample string to insert into the table you try and see what time it is taking to return the value
declare @i int =1
while @i<=10000
begin
-- Insert into table
Select 36, 'MNOPQRS'+Convert(varchar,@i), 'TESTING','XXX-XX-XXX','FNAME','LNAME','MNAME',NULL,'ADDRESS1','ADDRESS2','CITY','LP',98765,'XXX-XXX-XXXX',NULL,NULL,'1900-10-11 00:00:00.000','F','M',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'MNOPQRS',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2010-10-28 00:00:00.000',NULL,NULL,NULL,NULL,NULL,'All',NULL,NULL,NULL
set @i=@i+1
end
Thanks
Parthi
Thanks
Parthi
October 29, 2010 at 7:42 am
The majority of the time taken to run the query is probably taken up in moving that much data across the network for display on the client. From the information provided, it seems that the table contains about 40MB of data. SQL Server is able to read that amount from disk or memory very quickly, but transporting it to the client and actually displaying the results (perhaps in Management Studio?) may take much longer, depending on the speed of your network connection, and the speed of the client computer.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 29, 2010 at 7:52 am
To verify that the bottleneck is network/client speed, try running:
SELECT COUNT(*) FROM table WITH (INDEX(1));
That will force SQL Server to read every row in the table, but only return one summary row to the client.
If that query runs very much more quickly, you know that the 'problem' lies outside SQL Server.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 29, 2010 at 8:14 am
Paul White NZ (10/29/2010)
To verify that the bottleneck is network/client speed, try running:SELECT COUNT(*) FROM table WITH (INDEX(1));
That will force SQL Server to read every row in the table, but only return one summary row to the client.
If that query runs very much more quickly, you know that the 'problem' lies outside SQL Server.
The above gives the output less than 1 sec .Then what it mean here the problem is not with the sql or table you are saying as N/w or connectvity
Thanks
Parthi
Thanks
Parthi
October 29, 2010 at 8:32 am
Select * from sys.syscolumns 5 sec and in another server(B) it not even take a sec
Select * from sys.sysobjects 2 sec and in another server(B) it not even take a sec
so what is happining here. both are servers located at different place and im querying my server A is located at 1000 miles and another server B is located at about 1200 miles but are connected to WAN/LAN and im connecting at C (my work place )where i am trying to query the table the server B
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr 2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) (Hypervisor)
Server A
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
so where lies the problem
Thanks
Parthi
Thanks
Parthi
October 29, 2010 at 9:02 am
I had the same happening to me several year ago.
It turned out to be a port malfunction on the switch the server was connected to.
-- Gianluca Sartori
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply