 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / July 2008SQL Server: 8,421 max transactions per second?
i was wondering what an upper limit for SQL Server performance i should be looking at. i googled and found this article for SQL Server 2000: http://www.sqlmag.com/Article/ArticleID/16087/sql_server_16087.html "SQL Server 2000 Sails to New Speed Record " 505,302.77 transactions per minute Which works out to 8421 transactions per second. So if i was hoping for, say, 100,000 transactions per second, i would be expecting too much? It's safe to say that there is a limit on the performance of SQL Server for any given hardware configuration, no matter the DDL? What the max transactions per second you can get depends on so many different factors that any number without all the details is almost always meaningless. So for any given number, you can split the size of the transaction (to an extent) and therefore double the throughput without accomplishing anything. Linchi > i was wondering what an upper limit for SQL Server performance i should be > looking at. [quoted text clipped - 12 lines] > It's safe to say that there is a limit on the performance of SQL Server for > any given hardware configuration, no matter the DDL? Where to begin? 1) That article is from December, 2000, 7 1/2 years ago. 2) That article is talking about TPC-C transactions which is a very specialized and clearly defined set of work and not just a BeginTransaction - End Transaction pair. Maximum rate will depend on what you mean by a "transaction". 3) AFAIK, the current fastest reported result for TPC-C on Microsoft SQL Server is 1,231,433 TpmC, which is about 20,000 per second. See http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=105112801 4) And, yes, it is "safe to say that there is a limit on the performance of SQL Server for any given hardware configuration". I would say that's safe to say about any software of any type. Tom >i was wondering what an upper limit for SQL Server performance i should be >looking at. [quoted text clipped - 12 lines] > It's safe to say that there is a limit on the performance of SQL Server > for any given hardware configuration, no matter the DDL? The best SQL Server 2005 number is more than twice that: http://www.tpc.org/tpcc/results/tpcc_result_detail.asp?id=105112801 The best number currently in existence on tpc.org is 6M, which is 100K/sec. I do have to ask though why you think you need 100K/sec.  SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net >i was wondering what an upper limit for SQL Server performance i should be >looking at. [quoted text clipped - 12 lines] > It's safe to say that there is a limit on the performance of SQL Server > for any given hardware configuration, no matter the DDL? > I do have to ask though why you think you need 100K/sec. A select statement is being done: SELECT column2 FROM table WHERE column1=value1 AND column2=value2 column1 and column2 are covered by an index. Profiler shows the query taking 0 seconds for 22 reads, and 0 CPU. But issuing the select 40,000 times takes 137 seconds, which works out to about 3.4ms per SELECT. Which i guess is pretty good considering 100Mb LAN latency, there and back, query compilation time, hard drive seek times, etc. Nevertheless, i was hoping to trim that 137 seconds down to 5 or 6 seconds, which would be about 0.15ms per SELECT. i tried converting the select into a stored procedure, but it made no difference. Then it occurred to me that i'm probably just hitting a fundamental limit of SQL Server - and issuing a query that requires an index seek through 1M rows probably is going to take few thousand nano-seconds. So i went looking into ideas of upper limits on SQL Server's performance, so see if 3.4ms is outrageously high. > But issuing the select 40,000 times takes 137 seconds, which works out to > about 3.4ms per SELECT. Which i guess is pretty good considering 100Mb LAN > latency, there and back, query compilation time, hard drive seek times, etc. I think there are other limitations here that you have kind of considered but that are outside of SQL Server's control... 1) bandwidth 2) disk I/O capability (if the data is coming off disk) 3) memory (if the data is in memory) 4) returning / displaying on a client (there is a cost here as well) You should run the query with statistics I/O etc. on and see if you can narrow down how the processing time is being distributed. Compare with running the same queries directly on the SQL Server box (eliminating network). Compare with just a SQLCMD / osql command line SELECT (eliminating the overhead of SSMS). > Compare with running the same queries directly on the SQL Server box > (eliminating > network). Good idea. Remote: 3.4ms / select Local: 1.48ms / select So it's 1.92ms, or ~56% of it is network latency. Which is interesting as an excercise. >> Compare with running the same queries directly on the SQL Server box >> (eliminating [quoted text clipped - 8 lines] > >Which is interesting as an excercise. Another question is how the queries are submitted. Are there 40,000 batches? You might try running them in sets of ten, for example. Or SELECT column1, column2 FROM table WHERE column1=value1 AND column2=value2 UNION ALL SELECT column1, column2 FROM table WHERE column1=value1 AND column2=value3 UNION ALL SELECT column1, column2 FROM table WHERE column1=value1 AND column2=value4 Which could be written to return sets of five or ten of fifty. Roy Harvey Beacon Falls, CT >> I do have to ask though why you think you need 100K/sec. > [quoted text clipped - 14 lines] >which would be about 0.15ms per SELECT. i tried converting the select into a >stored procedure, but it made no difference. Hi Ian, If the query is *exactly* as above, a nonclustered index on the combination of columns column1 and column2 will be best. CREATE NONCLUSTERED INDEX xxx ON table(column1,column2) (Or, if the values are known beforehand, a materialized view specific for these values. Of course, optimizing for one specific test query will never increase performance of your real system...) If budget is unlimited, you can get better performance by buying lots of the fastest and best disk subsystems around, stuffing your server with memory and extra processors, etc. Then make sure to configure this all in the best possible way. Maybe add table and index partitioning too. Oh, and if you can query a read-only server, you'll gain a little extra performance because no locks have to be taken. Of course, this all isn't very realistic. But neither is the requirement of 100K transactions/sec. SignatureHugo Kornelis, SQL Server MVP My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis > If budget is unlimited, you can get better performance by buying lots of If budget is unlimited and it's read only, just buy enough memory and cache all the data in memory. Linchi > >> I do have to ask though why you think you need 100K/sec. > > [quoted text clipped - 36 lines] > Of course, this all isn't very realistic. But neither is the requirement > of 100K transactions/sec. Yes, and create covering indexes to avoid unnecessary table scans, because a table scan can be a good choice for an I/O bound system, but is usually a poor choice when all data is in memory. In such a secnario, bookmark lookups are usually a lot faster. SignatureGert-Jan > > If budget is unlimited, you can get better performance by buying lots of > If budget is unlimited and it's read only, just buy enough memory and cache [quoted text clipped - 46 lines] > > Hugo Kornelis, SQL Server MVP > > My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis > If the query is *exactly* as above, a nonclustered index on the > combination of columns column1 and column2 will be best. The *exact* query is: SELECT TOP 1 AccountNumber FROM EventPatronImports WHERE EventPatronImports.AccountNumber = 12345 AND EventPatronImports.EventPatronImportHeaderGUID = '{8C3AD9D8-DDD8-4F57-AAF0-485049C5F29E}' Well...not *exact*, you have to replace 12345 with an account number, and the guid i generated just now. But you get the idea. There is multiple account numbers for each import. i.e. EventPatronImprtHeaderGUID AccountNumber {A...} 1 {A...} 2 {A...} 3 {B...} 1 {B...} 2 {B...} 3 {C...} 1 {C...} 2 {C...} 3 So my non-clustered index is: CREATE NONCLUSTERED INDEX EventPatronImportAccountUniquePerImport ON EventPatronImports (EventPatronImportHeaderGUID, AccountNumber) i can see that the index already is in memory, so i can get less physical I/O than zero. > Of course, this all isn't very realistic. But neither is the requirement > of 100K transactions/sec. It's an import, that isn't using DTS, that i would like to change from taking 20 minutes to 20 seconds - cause nobody likes waiting. i realize it's unrealistic - which was the point of my OP. At some point you hit the limits of what's physically possible. i could completely re-write an import process that i didn't originally write. Half the time is spent doing a SELECT looking for a duplicate, the other half is doing an INSERT. Since i was doing a select on two columns covered by an index, i was wondering how much faster it can get than 22 logical reads with 0 cpu. But at some point you hit the laws of physics. The network can only switch the packets so fast, the query parser can only look at the unicode glyphs in the batch so fast, it takes time to find the pages already in memory, CPU only ticks so fast, etc. And if a customer says to Microsoft, "i'm building a product in which i need to go a single row indexed lookup query to take 150us". Microsoft would tell them to piss off. > And if a customer says to Microsoft, "i'm building a product in which i > need to go a single row indexed lookup query to take 150us". Microsoft > would tell them to piss off. I don't think they would tell them to piss off with that requirement. I believe that requirement is achievable given current hardware in the wintel world assuming the data existed in RAM to begin with. And it is still pretty close to achievable with an SSD if you have to do a seek to get it from said disk. Now, whether or not that initial request for data can be sent over the network from a client, processed, and the results sent back to the client in 150us is another matter, but those delays will exist in ANY environment, wintel, linux, unix, AIX, mainframe, etc. SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net >> If the query is *exactly* as above, a nonclustered index on the >> combination of columns column1 and column2 will be best. [quoted text clipped - 52 lines] > need to go a single row indexed lookup query to take 150us". Microsoft > would tell them to piss off. > those delays will exist in ANY environment, wintel, linux, unix, AIX, > mainframe, etc. That's my point. I am jumping in late on this discussion but wanted to add my 2 cents anyway:). Keep in mind that transactions per second mean different things to different people depending on what you are looking at. For instance the SQL Server Perfmon counter Trans Per second doesn't show the actual number of transactions as most people think of them and Batch requests per second doesn't give you that either. The TPC transaction is actually a group of "transactions" that are combined as 1 in the TPC numbers. So if you had 100 TPM it is really a set of (if I remember correctly) 4 or 5 operations such as a Select, a Delete, an Update etc. The number of statements you can execute in a second is very high in SQL Server and the limit is not a problem for most people who need such high numbers. You will not see 100K a second without spending a LOT of money on hardware regardless of if it is SQL Server, Oracle, DB2 etc. The requirement you have is unrealistic in that it should be redesigned to use some sort of bulk load or set based process to get the speeds you require. And unless you were issuing the exact same statement each time (same Guid & account #) you will have to compile a new plan each time which can take the time you want the query to execute in all by itself. You said a sp didn't help but I find that hard to believe. Actually I know it is not true if you tried to execute all the statements adhoc like that with as batches. It also sounds like you are on 2000 still which doesn't help. Bottom line is that if you want that much better numbers you will have to redesign. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors >> If the query is *exactly* as above, a nonclustered index on the >> combination of columns column1 and column2 will be best. [quoted text clipped - 52 lines] > need to go a single row indexed lookup query to take 150us". Microsoft > would tell them to piss off. > The requirement you have is unrealistic in that it should be redesigned Well of course SQL Server could do something else faster. i wasn't really asking about something else. Lets say, for the sake of arguemnt, that i do whatever anyone suggests, so that the load time is reduced from 20 minutes to 5 minutes, or perhaps 1 minute. Then, to continue the same line of question, i will ask can that be reduced to 20 seconds, to 2 seconds, to 2 ms. At some point you hit the limit of what a given operation can do. My question is how fast SQL Server can run: SELECT column1 FROM table WHERE colume1=value1 AND column2=value2 And the point is that it's okay to say, "You are doing everything that can be done. There's nothing further that could be done with the query or DDL to improve it." > You said a sp didn't help but I find that hard to believe. Actually I know > it is not true if you tried to execute all the statements adhoc like that > with as batches. Well, maybe a percent or two, nothing noticable, or worth deploying a DDL change. For 20,000 rows the SELECTs took about 137s, while the inserts took about 140s. Perhaps with a stored procedure the SELECTs take 135s. Nothing noticable. i was hoping that the bottleneck was SQL Server having to parse those 187 unicode glyphs. > It also sounds like you are on 2000 i did a test. 20,000 executes of SELECT CAST({ABCDEF...} AS uniqueidentifer) with a different GUID each time: SQL2000-1, T-SQL: 44986ms (vader, really old) SQL2000-2, T-SQL, 36,287ms (chewbacca, old) SQL2000-3, T-SQL: 22,987ms (jango) SQL2000-4, T-SQL: 21,373ms (mango, virtual machine running on solo) SQL2005, T-SQL: 16,005ms (solo, new cool hardware) Then i put that query in a stored procedure: CREATE PROCEDURE dbo.st @g uniqueidentifier AS SELECT @g and call the stored procedure through T-SQL: SQL2000-4: T-SQL/SP: 18,172ms (mango, virtual machine running on solo) Then i use an ADO (early binding) Command object to call the stored procedure with a new parameter each time: SQL2000-4: Command/SP: 17,577ms (mango, virtual machine running on solo) Using an SP gives a 17% improvment, not spectacular, not an order or magnitude. See in-line SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors >> The requirement you have is unrealistic in that it should be redesigned > [quoted text clipped - 6 lines] > be reduced to 20 seconds, to 2 seconds, to 2 ms. At some point you hit the > limit of what a given operation can do. Of course you hit a limit eventually but there are so many factors that come into play. The biggest of which is the hardware used. But it is not jsut a factor of how fast you can execute 1 statement but how fast you can execute many in parallel as well. That is where the savings in time will be. > My question is how fast SQL Server can run: > SELECT column1 FROM table [quoted text clipped - 3 lines] > be done. There's nothing further that could be done with the query or DDL > to improve it." Yes as long as you really have done everything you can such as proper indexing, proper disk config, server config etc. >> You said a sp didn't help but I find that hard to believe. Actually I >> know it is not true if you tried to execute all the statements adhoc like [quoted text clipped - 4 lines] > took about 140s. Perhaps with a stored procedure the SELECTs take 135s. > Nothing noticable. That is still hard to believe. Did you run this many times? In real life there would already be many thousands of query plans in the proc cache that would add to the issue. But I also wonder if you were calling the sp correctly from the client. Was it coming across as an RPC or a Batch? If it was not an RPC you have extra overhead to compile the batch for each execution as well and negate much of the benefit of having a sp in the first place. > i was hoping that the bottleneck was SQL Server having to parse those 187 > unicode glyphs. [quoted text clipped - 11 lines] > SQL2000-4, T-SQL: 21,373ms (mango, virtual machine running on solo) > SQL2005, T-SQL: 16,005ms (solo, new cool hardware) How did you call this? Was it 20,000 selects from the same client? > Then i put that query in a stored procedure: > CREATE PROCEDURE dbo.st @g uniqueidentifier AS [quoted text clipped - 10 lines] > Using an SP gives a 17% improvment, not spectacular, not an order or > magnitude. Again I question if it is an RPC or batch and I wonder how much of the time is in the client and the network. As stated before calling 20K executions to do womething that should be done in set based logic is like comparing apples to oranges. Going from adhoc code to a sp won't buy you much if the clinet is taking most of the time to make the calls. Did you profile the calls? What was the duration and such of the actual executions on SQL Server of the adhoc vs. sp? > That is still hard to believe. Did you run this many times? You can check my code. It's early-binding ADO using a command object for the parameterized queries. (It's Delphi, but easy enough to follow) Method 1: SQL Batch: 18.3s (e.g. SELECT {a...}) Method 2: Parameterized query: 17.3s (e.g. SELECT ?) Method 3: Parameterized stored procedure call: 16.8s Method 1: SQL Batch each time. 20,000 rows in 18,314 ms ========================================== procedure TForm1.Button1Click(Sender: TObject); var i: Integer; Conn: TADOConnection; szQuery: WideString; RecordsAffected: OleVariant; t1, t2: DWORD; rs: _Recordset; TotalTime: DWORD; begin Conn := TADOHelper.ConnectSQLServer(Edit3.Text, Edit1.Text, Edit2.Text); TotalTime := 0; for i := 1 to 20000 do begin szQuery := 'SELECT CAST('+GUIDToQuotedStr(CreateGUID)+' AS uniqueidentifier) AS Thingy'; t1 := GetTickCount; rs := Conn.ConnectionObject.Execute(szQuery, RecordsAffected, adCmdText); t2 := GetTickCount; TotalTime := TotalTime + (t2-t1); if (i mod 100) = 0 then begin Label5.Caption := IntToStr(i)+' rows in '+IntToStr(TotalTime)+' ms.'; Application.ProcessMessages; end; end; MessageDlg('Took '+IntToStr(TotalTime)+' ms to issue 20,000 SELECTs', mtInformation, [mbOk], 0); end; Method 2: Parameterized query. 20,000 rows in 17,331 ms ========================================== procedure TForm1.bbParameterClick(Sender: TObject); var i: Integer; Conn: TADOConnection; szQuery: string; Command: _Command; RecordsAffected: OleVariant; t1, t2: DWORD; rs: _Recordset; TotalTime: DWORD; begin Conn := TADOHelper.ConnectSQLServer(Edit3.Text, Edit1.Text, Edit2.Text); Command := CreateOleObject('ADOdb.Command') as _Command; Command.Set_ActiveConnection(Conn.ConnectionObject); Command.CommandType := adCmdText; szQuery := 'SELECT CAST(? AS uniqueidentifier) AS Thingy'; Command.CommandText := szQuery; Command.Parameters.Append(Command.CreateParameter('guid', adGUID, adParamInput, -1, EmptyParam)); TotalTime := 0; for i := 1 to 20000 do begin Command.Parameters['guid'].Value := GUIDToString(CreateGUID); t1 := GetTickCount; rs := Command.Execute(RecordsAffected, EmptyParam, 0); t2 := GetTickCount; TotalTime := TotalTime + (t2-t1); if (i mod 100) = 0 then begin Label6.Caption := IntToStr(i)+' rows in '+IntToStr(TotalTime)+' ms.'; Application.ProcessMessages; end; end; MessageDlg('Took '+IntToStr(TotalTime)+' ms to issue 20,000 SELECTs', mtInformation, [mbOk], 0); end; Method 3: Parameterized stored procedure Command call. 20,000 rows in 16,824 ms =========================================== procedure TForm1.bbStoredProcClick(Sender: TObject); var i: Integer; Conn: TADOConnection; szQuery: string; Command: _Command; RecordsAffected: OleVariant; t1, t2: DWORD; rs: _Recordset; TotalTime: DWORD; begin Conn := TADOHelper.ConnectSQLServer(Edit3.Text, Edit1.Text, Edit2.Text); TADOHelper.ExecuteNoRecords(Conn, 'USE '+Edit4.Text); Command := CreateOleObject('ADOdb.Command') as _Command; Command.Set_ActiveConnection(Conn.ConnectionObject); Command.CommandText := 'st'; Command.CommandType := adCmdStoredProc; Command.Parameters.Append(Command.CreateParameter('@g', adGUID, adParamInput, -1, EmptyParam)); Command.Prepared := True; TotalTime := 0; for i := 1 to 20000 do begin Command.Parameters['@g'].Value := GUIDToString(CreateGUID); t1 := GetTickCount; rs := Command.Execute(RecordsAffected, EmptyParam, 0); t2 := GetTickCount; TotalTime := TotalTime + (t2-t1); if (i mod 100) = 0 then begin Label8.Caption := IntToStr(i)+' rows in '+IntToStr(TotalTime)+' ms.'; Application.ProcessMessages; end; end; MessageDlg('Took '+IntToStr(TotalTime)+' ms to issue 20,000 SELECTs', mtInformation, [mbOk], 0); end; If you made it down this far, then it is VERY interesting to note that if method 1 (T-SQL batch, un-parameterized) was done through the Command object, it would take over 40s. Switching to use the Connection object itself cut the time in half. > Was it coming across as an RPC or a Batch? Yes. Both. Batch for unparameterized, rpc for parameterized > How did you call this? Was it 20,000 selects from the same client? Click button1, wait for 20,000 executes. Click button2, wait for 20,000 executes. Click button3, wait for 20,000 executes. ADO Command object with parameters. Profiler shows: Method 1: SQL:BatchCompleted SELECT CAST('{D1D83341-4336-44E7-ADAA-2FFCA9321969}' AS uniqueidentifier) AS Thingy Project1.exe sa 0 2 0 0 2136 60 2008-07-15 18:03:42.610 SQL:BatchCompleted SELECT CAST('{EF545A57-7583-483B-B5BA-5717107F7FA6}' AS uniqueidentifier) AS Thingy Project1.exe sa 0 2 0 0 2136 60 2008-07-15 18:03:42.610 SQL:BatchCompleted SELECT CAST('{A9E11EAB-9985-4986-8723-06E17B3D1644}' AS uniqueidentifier) AS Thingy Project1.exe sa 0 2 0 0 2136 60 2008-07-15 18:03:42.610 etc Method 2: RPC:Completed exec sp_execute 1, '805675A7-0925-4B04-9AFC-077568F74510' Project1.exe sa 0 0 0 0 2136 57 2008-07-15 18:03:01.160 RPC:Completed exec sp_execute 1, '2E7E768B-BF7D-4F2D-B0AB-6961FF044DC2' Project1.exe sa 0 0 0 0 2136 57 2008-07-15 18:03:01.160 RPC:Completed exec sp_execute 1, 'CE145D2D-F0F2-4675-9290-4821217924FD' Project1.exe sa 0 0 0 0 2136 57 2008-07-15 18:03:01.160 etc Method 3: RPC:Completed exec sp_execute 1, '14665F05-33AA-465D-A5DE-80593F111327' Project1.exe sa 0 0 0 0 2136 67 2008-07-15 18:04:10.710 RPC:Completed exec sp_execute 1, '92427D43-298C-415A-B849-4D3B19D26B5E' Project1.exe sa 0 0 0 0 2136 67 2008-07-15 18:04:10.710 RPC:Completed exec sp_execute 1, '0AA12CE9-555E-4977-979A-77FC57DD0468' Project1.exe sa 0 0 0 0 2136 67 2008-07-15 18:04:10.710 etc > Did you profile the calls? > What was the duration and such of the actual executions on SQL Server of > the adhoc vs. sp? Unfortunatly since SQL Profiler isn't using the high resolution timer, most of the calls duration is "0". About 100 of them have a duration of "10". Not much help for these small single calls. Although interesting to note that each Batch call took 2 reads, all parameterized calls took zero reads. 6pm. Soo hungry, going home now. Ian, OK this is where Delphi tends to get in the way sometimes. It appears that for Method 2 & 3 it is actually doing a sp_prepare to prepare the query and then issuing a sp_execute once for each iteration. In the case of method #2 that might be expected and is OK and that is why it is coming across as a RPC. The alternative is to call sp_executesql each time which is what you would get with straight ADO or .net. But for method #3 that is not the same as calling a sp. It is also preparing the stored procedure calls which it should not. You should see EXEC st and not exec sp_exec. Method 1: SQL:BatchCompleted SELECT CAST('{D1D83341-4336-44E7-ADAA-2FFCA9321969}' AS uniqueidentifier) AS Thingy Project1.exe sa 0 2 0 0 2136 60 2008-07-15 Method 2: RPC:Completed exec sp_execute 1, '805675A7-0925-4B04-9AFC-077568F74510' Project1.exe sa 0 0 0 0 2136 57 2008-07-15 18:03:01.160 Method 3: RPC:Completed exec sp_execute 1, '14665F05-33AA-465D-A5DE-80593F111327' Project1.exe sa 0 0 0 0 2136 67 2008-07-15 18:04:10.710 <<<<< Unfortunatly since SQL Profiler isn't using the high resolution timer, most of the calls duration is "0". About 100 of them have a duration of "10". Not much help for these small single calls. <<<< If you use SQL2005 you can get it in microseconds and see the actual durations. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors >> That is still hard to believe. Did you run this many times? > [quoted text clipped - 191 lines] > > 6pm. Soo hungry, going home now. > OK this is where Delphi tends to get in the way sometimes. It appears that > for Method 2 & 3 it is actually doing a sp_prepare to prepare the query [quoted text clipped - 4 lines] > is not the same as calling a sp. It is also preparing the stored procedure > calls which it should not. You should see EXEC st and not exec sp_exec. This is straight ADO, i'm using the interface directly. You can say that "it shouldn't be doing that", but if that what the OLEdb Provider for SQL Server decides to do, there's not much can be done about it. Show me early binding code you would use (e.g. C++) First time Execute is run: ================= RPC:Starting declare @P1 int set @P1=NULL exec sp_prepexecrpc @P1 output, N'st', 'D483532A-D53A-4C47-AD87-7150B7D1D780' select @P1 SP:CacheMiss SP:CacheMiss SP:CacheInsert SP:Starting exec sp_prepexecrpc 'D483532A-D53A-4C47-AD87-7150B7D1D780' SQL:StmtStarting -- st SELECT @g SQL:StmtCompleted -- st SELECT @g SP:Completed exec sp_prepexecrpc 'D483532A-D53A-4C47-AD87-7150B7D1D780' RPC:Completed declare @P1 int set @P1=1 exec sp_prepexecrpc @P1 output, N'st', 'D483532A-D53A-4C47-AD87-7150B7D1D780' select @P1 RPC Output Parameter 1 Second time Execute is run: ======================= SQL:BatchStarting SET NO_BROWSETABLE ON SQL:StmtStarting SET NO_BROWSETABLE ON SQL:StmtCompleted SET NO_BROWSETABLE ON SQL:BatchCompleted SET NO_BROWSETABLE ON SQL:BatchStarting SET FMTONLY ON exec sp_execute 1,'00000000-0000-0000-0000-000000000000' SET FMTONLY OFF SQL:StmtStarting SET FMTONLY ON SQL:StmtCompleted SET FMTONLY ON SQL:StmtStarting exec sp_execute 1,'00000000-0000-0000-0000-000000000000' SP:CacheMiss SP:CacheMiss SP:CacheMiss SP:CacheMiss SP:CacheInsert SP:Starting SQL:StmtStarting -- st SELECT @g SQL:StmtCompleted -- st SELECT @g SP:Completed Exec Prepared SQL SQL:StmtCompleted exec sp_execute 1,'00000000-0000-0000-0000-000000000000' SQL:StmtStarting SET FMTONLY OFF SQL:StmtCompleted SET FMTONLY OFF SQL:BatchCompleted SET FMTONLY ON exec sp_execute 1,'00000000-0000-0000-0000-000000000000' RPC:Starting exec sp_execute 1, 'A53391C5-47FC-4387-B33E-A1ED15EBF7D8' SP:ExecContextHit SP:Starting exec sp_execute 'A53391C5-47FC-4387-B33E-A1ED15EBF7D8' SQL:StmtStarting -- st SELECT @g SQL:StmtCompleted -- st SELECT @g SP:Completed exec sp_execute 'A53391C5-47FC-4387-B33E-A1ED15EBF7D8' Exec Prepared SQL RPC:Completed exec sp_execute 1, 'A53391C5-47FC-4387-B33E-A1ED15EBF7D8' Third time Execute is run: ==================== RPC:Starting exec sp_execute 1, '297013AC-4C4A-4CBC-AE32-50CB209DDC48' SP:ExecContextHit SP:Starting exec sp_execute '297013AC-4C4A-4CBC-AE32-50CB209DDC48' SQL:StmtStarting -- st SELECT @g SQL:StmtCompleted -- st SELECT @g SP:Completed exec sp_execute '297013AC-4C4A-4CBC-AE32-50CB209DDC48' Exec Prepared SQL RPC:Completed exec sp_execute 1, '297013AC-4C4A-4CBC-AE32-50CB209DDC48' Fourth time Execute is run: =================== RPC:Starting exec sp_execute 1, '328D621D-29F2-45E6-8D54-BCAF5699CD33' SP:ExecContextHit SP:Starting exec sp_execute '328D621D-29F2-45E6-8D54-BCAF5699CD33' SQL:StmtStarting -- st SELECT @g SQL:StmtCompleted -- st SELECT @g SP:Completed exec sp_execute '328D621D-29F2-45E6-8D54-BCAF5699CD33' Exec Prepared SQL RPC:Completed exec sp_execute 1, '328D621D-29F2-45E6-8D54-BCAF5699CD33' Feel free to whip up your own test program, if you think ADO isn't doing it right. Otherwise i'll just be starting another thread in this group asking why ADO isn't doing it right. I see now. You have this line set: Command.Prepared := True; Try setting that to false and see what you get. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors >> OK this is where Delphi tends to get in the way sometimes. It appears >> that for Method 2 & 3 it is actually doing a sp_prepare to prepare the [quoted text clipped - 141 lines] > it right. Otherwise i'll just be starting another thread in this group > asking why ADO isn't doing it right. >I see now. You have this line set: Command.Prepared := True; Try >setting that to false and see what you get. The docs give the impression that setting Prepared to true would hit the sever and prepare it - but nothing happens on the server during the call to prepare. i freed the proc cache, and then do it again with the call to Prepared = True removed: First call to Execute: ================ RPC:Starting exec st 'D81EFCEA-4760-4A20-95E1-DF7C7F1CEFE3' SP:CacheMiss SP:CacheMiss SP:CacheInsert SP:Starting exec st 'D81EFCEA-4760-4A20-95E1-DF7C7F1CEFE3' SQL:StmtStarting -- st SELECT @g SQL:StmtCompleted -- st SELECT @g SP:Completed exec st 'D81EFCEA-4760-4A20-95E1-DF7C7F1CEFE3' RPC:Completed exec st 'D81EFCEA-4760-4A20-95E1-DF7C7F1CEFE3' Second call to execute: ================== SQL:BatchStarting SET NO_BROWSETABLE ON SQL:StmtStarting SET NO_BROWSETABLE ON SQL:StmtCompleted SET NO_BROWSETABLE ON SQL:BatchCompleted SET NO_BROWSETABLE ON SQL:BatchStarting SET FMTONLY ON EXEC st '00000000-0000-0000-0000-000000000000' SET FMTONLY OFF SQL:StmtStarting SET FMTONLY ON SQL:StmtCompleted SET FMTONLY ON SQL:StmtStarting EXEC st '00000000-0000-0000-0000-000000000000' SP:CacheMiss SP:CacheMiss SP:CacheInsert SP:Starting EXEC st '00000000-0000-0000-0000-000000000000' SQL:StmtStarting -- st SELECT @g SQL:StmtCompleted -- st SELECT @g SP:Completed EXEC st '00000000-0000-0000-0000-000000000000' SQL:StmtCompleted EXEC st '00000000-0000-0000-0000-000000000000' SQL:StmtStarting SET FMTONLY OFF SQL:StmtCompleted SET FMTONLY OFF SQL:BatchCompleted SET FMTONLY ON EXEC st '00000000-0000-0000-0000-000000000000' SET FMTONLY OFF SQL:BatchStarting SET NO_BROWSETABLE OFF SQL:StmtStarting SET NO_BROWSETABLE OFF SQL:StmtCompleted SET NO_BROWSETABLE OFF SQL:BatchCompleted SET NO_BROWSETABLE OFF RPC:Starting exec st 'F827B9BA-E5AF-463A-AEA5-F6A2650F8BE6' SP:ExecContextHit SP:Starting exec st 'F827B9BA-E5AF-463A-AEA5-F6A2650F8BE6' SQL:StmtStarting -- st SELECT @g SQL:StmtCompleted -- st SELECT @g SP:Completed exec st 'F827B9BA-E5AF-463A-AEA5-F6A2650F8BE6' RPC:Completed exec st 'F827B9BA-E5AF-463A-AEA5-F6A2650F8BE6' Third call to Execute: ================ RPC:Starting exec st 'CDCC2CA1-2508-4858-A382-D7E8EDD2DAEC' SP:ExecContextHit SP:Starting exec st 'CDCC2CA1-2508-4858-A382-D7E8EDD2DAEC' SQL:StmtStarting -- st SELECT @g SQL:StmtCompleted -- st SELECT @g SP:Completed exec st 'CDCC2CA1-2508-4858-A382-D7E8EDD2DAEC' RPC:Completed exec st 'CDCC2CA1-2508-4858-A382-D7E8EDD2DAEC' Fourth call to Execute ================ RPC:Starting exec st '0B57210B-64F0-44EE-9C98-F19FF29C4BE9' SP:ExecContextHit SP:Starting exec st '0B57210B-64F0-44EE-9C98-F19FF29C4BE9' SQL:StmtStarting -- st SELECT @g SQL:StmtCompleted -- st SELECT @g SP:Completed exec st '0B57210B-64F0-44EE-9C98-F19FF29C4BE9' RPC:Completed exec st '0B57210B-64F0-44EE-9C98-F19FF29C4BE9' So by not preparing the statement, is it running my original stored procedure, rather running a stored procedure to run the stored procedure? btw, the stored procedure name "st" means Speed Test, but i wanted to shave 9 bytes off the call name Speed Results, 20,000 executes. Run #1 ======================= Ad-hoc query: 25,280ms Parameterized Query: 20,551ms Parameterized Query (Prepared): 20,910ms Stored Procedure: 22,004ms Stored Procedure (Prepared): 22,560ms Run #2 ======================= Ad-hoc query: 24,544ms Parameterized Query: 21,511ms Parameterized Query (Prepared): 22,375ms Stored Procedure: 22,531ms Stored Procedure (Prepared): 22,137ms Run #3 ======================= Ad-hoc query: 26,126ms Parameterized Query: 20,547ms Parameterized Query (Prepared): 21,948ms Stored Procedure: 22,341ms Stored Procedure (Prepared): 22,877ms Averages: Ad-hoc query: 25317 ms (stdev 791) Parameterized Query: 20870 ms (stdev 555) Parameterized Query (Prepared): 21744 ms (stdev 753) Stored Procedure: 22,292 ms (stdev 266) Stored Procedure (Prepared): 22524 ms (stdev 371) The parameterized versions are 13.6% faster than the ad-hoc. But the parameterized, un-prepared, non-stored procedure call is 5.9% faster than the others. Interesting, surprising, and no double unbelieveable. OK well the unprepare sp calls look more like what I expect although you still have a lot of the garbage that comes along with classic ADO and the earlier ODBC drivers. The things like SET NO_BROWSETABLE ON & SET FMTONLY OFF are the results of piss poor coding on the drivers part and are not used in the newer drivers for .net. But I think you are still mixing up two important factors of how long it takes to execute each iteration. We have the time it takes for the client to start the call, wait for the return, process it and then go on to the next call. The times you are measuring include all of that of which xx many milli or mico seconds are related to the client and the round trip including the network. If that time is significant enough the performance changes on the SQL Server side may be overshadowed to a degree that is significant. This is why I was asking for the duration in Microseconds from trace for each execution. Then you can see how much of the time is related to the client calls vs. just SQL Server. Another thing to consider is that from what I can tell you don't have SET NOCOUNT ON for that session or in the sp. That can make a significant difference in total time because it can suppress at least one round trip per call by eliminating the rows affected message. The addition of the SELECT @g may also add a significant time to the totals. This will add another rowset and round trip by itself and if you don't have SET NOCOUNT ON it can add yet another round trip or message to be processed. In a situation like this (well tuned query & indexes) the client and network round trips are almost always the limiting factors in how fast you can process a large number of individual statements. And another factor that you probably aren't running into with these small scale test is that when you use adhoc queries like that you can get another very significant performance hit if the plans are cached and not reused. If so then each time you call with a different guid it will have to look up to see if the plan is already in the cache. Since they all are similar they may fit into the same hash bucket that is used to find the plans. From there they are compared char by char to see if they are really a match. The more plans the longer this can take. If you end up with 10's or 100's of thousands of similar plans the time to look up the plan can take seconds per call. 2005 and 2008 handle this much better than 2000 did but the potential still exists. SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors > >I see now. You have this line set: Command.Prepared := True; Try > >setting that to false and see what you get. [quoted text clipped - 171 lines] > > Interesting, surprising, and no double unbelieveable. > We have the time it takes for the client to start the call, wait for the > return, process it and then go on to the next call. Which are unavoidable - i accept. > This is why I was asking for the duration in Microseconds from trace for > each execution. i switched my test app to use the high-resolution timer. (Profiler cannot show microseconds) The numbers are very dependant on server load at any given second. But there's no difference between SET NOCOUNT OFF and SET NOCOUNT ON whether it's being run locally on the server or remotely. It does run faster when i run it locally on the server. > i switched my test app to use the high-resolution timer. (Profiler cannot > show microseconds) Profiler in 2005 can. There is an option to display it both ways. By default it is milliseconds but you can change it. The trace is always captured in microseconds though. A server side trace is actually the best way to do this testing since Profiler itself can affect the performance. What do you mean by running locally? Do you mean the client is running on the server? SignatureAndrew J. Kelly SQL MVP Solid Quality Mentors >> We have the time it takes for the client to start the call, wait for the >> return, process it and then go on to the next call. [quoted text clipped - 12 lines] > > It does run faster when i run it locally on the server. > What do you mean by running locally? Do you mean the client is running on > the server? Yes. Locally was local the SQL Server. Remotely was on my desktop machine. (i was aware of the potential for confusion when i wrote it - since local to the SQL Server is remote to me) >>But issuing the select 40,000 times takes 137 seconds, which works out to >>about 3.4ms per SELECT. Which i guess is pretty good considering 100Mb LAN [quoted text clipped - 3 lines] >>which would be about 0.15ms per SELECT. i tried converting the select into a >>stored procedure, but it made no difference. >The *exact* query is: > [quoted text clipped - 15 lines] >{B...} 1 >{B...} 2 I think you need to consider a different approach. With 40,000 tests to make I would be looking to do some sort of bulk load to a work table and one SELECT that joins the temp table to the production table, and one result set with the complete picture for all 40,000 rows. If I absolutely had to deal with the 40,000 as individual queries I would look to open multiple connections and see if running them in parallel helps. If you want to cut the time by a factor of twenty I guess you would want 20 connections as a starting point. You might reach a point of diminishing returns before that - or not - but I think there is some performance improvement to be found by going parallel. Roy Harvey Beacon Falls, CT > If you want to cut the time by a factor of twenty I > guess you would want 20 connections as a starting point. It was more of an idle wish. Also, understand that the original query was: SELECT TOP 1 * FROM table WHERE column1=value1 AND column2=value2 Change Nº1 - only select an important column SELECT TOP 1 column1 FROM table WHERE column1=value1 AND column2=value2 Change Nº2 - Add covering index on (column1, column2). The test is only used as an existance test (do any rows come back). i tried using a stored procedure CREATE CheckForThing @value1 int @value2 uniqueidentifier AS IF EXISTS( SELECT * FROM table WHERE column1 = @value1 AND column2 = @value2) BEGIN SELECT 1 AS RowExists END END but that didn't give any improvements. i suppose i could also try changing the query to: SELECT 1 AS RowExists FROM table WHERE column1 = value1 AND column2 = value2 In order to return a one-byte tinyint rather than a four byte integer - saving myself 3 bytes on the network packet. Although by aliasing the field i'm sending an extra 9 bytes. So another change could be: SELECT 1 FROM table WHERE column1=value1 AND column2 = value2 So now i'm only returning the one-byte tinyint, rather than 11 bytes for the value of column1 and the name of column1 "column1". But in fact that didn't make much of a difference. Probably because (as Larry Osterman points out http://blogs.msdn.com/larryosterman/archive/2006/05/08/592763.aspx) it's not the number of bytes, its the number of packets. "...it takes essentially the same time to send one byte of data as it does to send 1K..." (snip) >So my non-clustered index is: >CREATE NONCLUSTERED INDEX EventPatronImportAccountUniquePerImport ON >EventPatronImports (EventPatronImportHeaderGUID, AccountNumber) Hi Ian, If the combination of EventPatronImportHeaderGUID and AccountNumber is unique, then you could try defining the index as a unique index so that SQL Server knows there'll never be more than one match. On the other hand, with the TOP 1 in the query I doubt if it helps - but it never hurts to try. Also, how many bytes is the current clustered index of the table? SignatureHugo Kornelis, SQL Server MVP My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis > If the combination of EventPatronImportHeaderGUID and AccountNumber is > unique It's not. > with the TOP 1 in the query I doubt if it helps Me too. > Also, how many bytes is the current clustered index of the table? No primary key. Non-unique clustered key on (EventPatronImportHeaderGUID, PatronGUID). So...16 bytes + 16 bytes + [size of uniqueifier, 4 bytes?] >.> <.< Don't say it! (snip) >> Also, how many bytes is the current clustered index of the table? > >No primary key. Non-unique clustered key on (EventPatronImportHeaderGUID, >PatronGUID). > >So...16 bytes + 16 bytes + [size of uniqueifier, 4 bytes?] Hi Ian, I guess that means that you can gain a tiny extra speed advantage by either defining a much shorter clustered index, or not using any clustered index at all. Since the clustered index key (or, if there is no clustered index, the RID) is stored on the leaf level of each nonclustered index, shortining the clustered index key might result in more index rows fitting in each page and thus less pages being processed. Of course, this will probably hurt other queries (a lot!!). As I already said before, optimizing a single query this heavily and at the expense of overall performance isn't exactly a realistic scenario. >>.> ><.< > >Don't say it! Say what? SignatureHugo Kornelis, SQL Server MVP My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.