0

I have a table as large as 8GB and with 20M records. There is an int field called mth. The mth field stores date information in form of YYYYMM, I want to transform the mth field to consecutive integers. So I use a formula to get the year and month from field mth and calculate the month order, specifically, I use the following code:

create function mth_to_num(@month int)
returns int
as 
begin
   return(round(@month/100,0)*12+@month-100*round(@month/100,0))
end

Then I use the following code to update the value in the huge table

update full_orig_month_Q1_1999 
set mth_order = dbo.mth_to_num(period)
Go 

However, the code took quite long time to execute, roughly 2-3 minutes. My system is windows 10 64 bit with SQL server 2016. Is there any way I can speed it up?

Another problem is that I found SQL server occupying as much as 8GB in the database after executing the above query. Does it need so much memory? How can I release them?

Thanks for your help in advance!

Jason

  • Speed looks good for updating 20M records. SQL Server will not release memory once acquired, but that depends on your MIN and MAX memory settings. Just because sqlserver.exe has 8GB memory usage does not mean it is actually currently using it (Buffer Manager, Plan Cache, etc.). I've found this to be a general misunderstanding, when non-knowledgeable people look at the task manager. You could trace the statement with SQL Server Profiler or view the Query Plan. SQL Server 2016 can show Live Query Statistics. What is your SQL Server Edition? Standard, Enterprise, Express, Developer? – John aka hot2use Nov 4 '16 at 5:57
  • @hot2use, thanks for your comments! I'm using the developer edition. I just started manipulating large data using SQL server and am eager to learn writing efficient sql query. Could you kindly recommend me some resources to study? Thanks a lot! – Jason Nov 4 '16 at 14:13
5

When possible don't use scalar user defined functions (UDF) in queries. They force the entire plan to be serial, they can cause a lot of additional CPU resources to be used, they are a black box to the optimizer and can lead to cardinality estimation issues in other parts of the plan, and they can result in needlessly large memory grants.

Try running your UPDATE query without the UDF. Just take the code that you have and put it into the UPDATE query directly. Something like this:

update full_orig_month_Q1_1999 
set mth_order = round(period/100,0)*12+period-100*round(period/100,0);

Does that improve the runtime and memory usage?

  • Yes (but remove that return!). If period is an integer, they don't need round() either. SET mth_order = period - 88 * (period/100); would work just the same, without any function calls. – ypercubeᵀᴹ Nov 3 '16 at 23:10
  • Thanks for your help! It's indeed much faster. Using user defined function, it takes 2 and a half minutes. Using inline expression, it only takes 1 and half minutes. – Jason Nov 4 '16 at 1:10
  • I think the arithmetic operations only takes a couple of seconds in this case I tested the same operation with R and it only takes seconds. Then what's the bottle neck in this case? How can I improve it? Thanks a lot for your help! – Jason Nov 4 '16 at 1:12
  • The bottleneck is probably writing the change to the transaction log. – mendosi Nov 4 '16 at 2:23
  • @mendosi, is there any way we can turn it off? – Jason Nov 4 '16 at 2:36
0

You could try using Paul Randall's script to capture the Wait Statistics occurring during the execution of your script.

Based on the results that are returned you can then search top Wait Types and interpret the performance issues.

Sample results could look like this:

WaitType    Wait_S  Resource_S  Signal_S    WaitCount   Percentage  AvgWait_S   AvgRes_S    AvgSig_S
ASYNC_NETWORK_IO    0.00    0.00    0.00    2   100.00  0.0005  0.0005  0.0000

Searching for ASYNC_NETWORK_IO would then reveal for example:

This wait type is where SQL Server has sent some data to a client through TDS and is waiting for the client to acknowledge that is has consumed the data, and can also show up with transaction replication if the Log Reader Agent job is running slowly for some reason. (Books Online description: “Occurs on network writes when the task is blocked behind the network. Verify that the client is processing data from the server.”)

In response to your comment:

Paul Randall's and Brent Ozar's sites are good starting points for performance tuning and optimizing. And I still find that the SQL Server 2008 R2 Books Online (which can still be downloaded) contain heaps of information regarding database internals even though some of it is outdated.

Aaron Bertrand has some good information on performance tuning and lots of other people equally so.

  • Thanks a lot for your comment! I'll check out these resources! – Jason Nov 4 '16 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.