none
Same Query-Slower Processing

    Question

  • I needed to run a query against 42000 records of data which checks each record and validates it against a number of conditions before flagging it as ok to process or not.

    Before starting I took a copy of the database to my local laptop and developed a query which, once complete, ran in just over 15 minutes.

    Happy that all was ok with the query I then ran it on our server against the original set of data. This took 37 minutes for the exact same query on the exact same database.

    Both are running SQL Server 2008R2. The difference is my laptop is Developer Edition whilst the server is Standard Edition. Could this make such a massive difference in the time?

    What surprised me more is that my laptop is an Intel i7 2.6Ghz processor with 16GB RAM running Windows 7 64 Bit SQL Server Dev 10.50.4000 and the server is Intel Xeon E5-4603 @ 2.00Ghz with 16 Cores 64GB RAM Windows Server 2008R2 64 Bit SQL Server Std 10.50.4000.

    Any suggestions on why such a difference should occur when the server is a much more powerful machine yet seems to run the same query nearly 3 times slower?

    Any advise would be much appreciated.

    Tuesday, December 17, 2013 3:41 PM

Answers

  • >my laptop is an Intel i7 2.6Ghz processor with 16GB RAM ...

    >the server is Intel Xeon E5-4603 @ 2.00Ghz with 16 Cores 64GB RAM

    The server is not faster than your laptop, it has more, but slower cores.

    Also the IO, especially the log file IO can be different and slower in a server environment.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, December 17, 2013 4:07 PM

All replies

  • Are other people querying/updating the table(s) you are querying?  Are there other queries running on the server?  You query may be blocked by these other processes.

    Are you running the query from SQL Server Management studio on your laptop?  Then data has to cross the network to get to you machine.

    Are the tables fragmented on your server and not on your lap top.  You could try adding the following statement before your query on the server and on your laptop. http://technet.microsoft.com/en-us/library/ms184361.aspx

    set statistics io on;

    Then when done,

    set statistics io off;

     Do the results on the server record a higher logical reads than the same query on your laptop?  If so, you may have fragmentation on the server that is not on the laptop.

    Others will have ideas well.

     

     

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Tuesday, December 17, 2013 3:50 PM
  • >my laptop is an Intel i7 2.6Ghz processor with 16GB RAM ...

    >the server is Intel Xeon E5-4603 @ 2.00Ghz with 16 Cores 64GB RAM

    The server is not faster than your laptop, it has more, but slower cores.

    Also the IO, especially the log file IO can be different and slower in a server environment.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, December 17, 2013 4:07 PM
  • The performance is in the same order of magnitude, so nothing to worry about there.

    The circumstances of you local laptop and a (shared?) server are quite different. Apart from the CPU power and SQL Server edition, there are a lot of other resources that can be relevant, such as network latency, I/O, disk contention, etc.

    From your description of the purpose and the fact that it takes a massive 15 minutes to process just 42000 rows, it suggests that you have implemented row by row processing (as opposed to a set based approach).

    If you run such cursor-like solution against a remote database, then the setting "SET NOCOUNT ON" could already make the difference between 37 minutes and less than 15 minutes.


    Gert-Jan

    Wednesday, December 18, 2013 12:18 PM
  • Thank you for the replies.

    I appreciate there are differences between the machines but I was just surprised by the significant differences between the two on what is the same process on the same data. A couple of minutes either way and I wouldn't have been surprised but it seemed so different I wondered if the SQL Version could make that much difference or there would be some "magic switch" that wasn't set on the server! We can hope!!!

    There have been a couple of suggestions of things to try so I will take a look over the Christmas period when I've some time.

    Thanks again for taking a look and replying.

    Wednesday, December 18, 2013 1:50 PM