sql query execution takes more time.

Question

Actually my Query Execution takes 44 seconds with only 195 records how can reduce the time to milliseconds.

I have one of the table i.e. "table1" is stored in two servers . in the server one "table1" table takes only 2 milliseconds of time to execute the query but the same table which is in the server2 takes 44 seconds .
Why it is taking so much of time in second Server ?

Could You please any one give me an answer.......

thank you,

Answer 1

Hi,

but the same table

According to your description these tables are in two different instances/database which mean these cannot be the same table. You probably means that the two table has the same structure since you see that the tables has the same columns when you execute query

The fact is that this means nothing regarding the real structure of the table behind the scenes. For example you might have hidden columns which take space in each row.

For more information on such cases, search google for: table structure ronen ariely

There are recording in Hebrew and in English from different conferences.

Note! This can be fixed usually by ALTER TABLE... REBUILD as presented in the recordings.

stored in two servers

This mean that you do not use the same connection and you do not connect to the same server.

Differences can be related to connection properties (connection string) for example and to server setting.

Why it is taking so much of time in second Server ?

Without the ability to connect to your server or read minds how can anyone answer this?!?

We can only guess and/or lead you to bring us more information, which is what we will need to do next :-)

(1) Please provide the exact query which you run.

(2) Please provide the full queries to create both tables including hidden objects like indexes, triggers, constraints, relations to other tables (all these and more can make a different).

Get this information from the SSMS by right click on the table name in the Object Explorer

(3) Compare the data in both table and make sure the number of rows is the same

(4) Check the size of the data in both tables (related to what I mentioned above)

You can do this by right click on the table name in the SSMS Object Explorer -> select properties and move to the "Storage" tab

Or much better option to get size of related indexes and table raw data is to use queries to get the information

SELECT   
    t.NAME AS TableName,  
    s.Name AS SchemaName,  
    p.rows,  
    SUM(a.total_pages) * 8 AS TotalSpaceKB,  
    SUM(a.used_pages) * 8 AS UsedSpaceKB  
FROM   
    sys.tables t  
INNER JOIN        
    sys.indexes i ON t.OBJECT_ID = i.object_id  
INNER JOIN   
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id  
INNER JOIN   
    sys.allocation_units a ON p.partition_id = a.container_id  
LEFT OUTER JOIN   
    sys.schemas s ON t.schema_id = s.schema_id  
WHERE t.NAME = 'Person' -------<<<<< Change the table name to your table  
GROUP BY   
    t.Name, s.Name, p.Rows  
GO  

Please provide all the information and we will continue from there

Answer 2

Hi @karunakar Panja ,

If you can trace exec plan using profiler, can you find that the plan is the same both times, sometimes small differences can create a different environment for query execution, the assumption is that the database are the same and the data and statistics are the same, but the system or database setup is different, these different can lead to different execution plans and thus different execution times

You can compare your execution plan, and compare the properties of the operator, this is perhaps the quickest way to identify issues that may cause discrepancies between servers.

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.