May 15, 2010 at 5:43 am
Hi-
I am being probably over-expecting here but anyway..
I run a stored procedure from my C# app. I want to be able to run that stored procedure for 10000 times a second with some margin in time if that's even possible..
Right now, under best circumstances (only my app monopolizing cpu without any competition), it takes 2.7 seconds. So, just to be sure I removed ALL the code in stored procedure and ran it. It took almost 1.3 seconds to run a empty stored procedure.
I am using SQL Server Express 2008 on the local machine with only Shared Memory protocol enabled.
Since this communication itself takes more than one second, I stand no chance to achieve my object by query tuning.
Firstly, I wanted to know whether it is expectedormal to require 1.3 seconds for 10000 stored procedure calls. If general result is that it takes lesser amount of time, I wanted to know how can I improve.
The empty stored procedure I tried had no parameters and it returned hard coded -1.
Thanks for your help in advance,
-Neel.
May 15, 2010 at 6:12 am
Hi Neel;
I'd expect anything called 10K times to be in the realm of a max of 400 items per second; so for 10K calls, i'd expect that to take 25 seconds or longer.
anything being run 10K times per seconds would worry me;
if you are doing the same thing over and over again, chances are it could be replaced with a set based operation that does it once....it really depends on what the stored proc is /was doing.
can you explain what the stored proc was doing, and show us the code?
we can probably offer a better solution to the issue at hand, instead of trying to optimize the current repetitive calls of the proc.
[/quote]
Lowell
May 15, 2010 at 5:50 pm
neelsmail (5/15/2010)
Hi-I am being probably over-expecting here but anyway..
I run a stored procedure from my C# app. I want to be able to run that stored procedure for 10000 times a second with some margin in time if that's even possible..
Right now, under best circumstances (only my app monopolizing cpu without any competition), it takes 2.7 seconds. So, just to be sure I removed ALL the code in stored procedure and ran it. It took almost 1.3 seconds to run a empty stored procedure.
I am using SQL Server Express 2008 on the local machine with only Shared Memory protocol enabled.
Since this communication itself takes more than one second, I stand no chance to achieve my object by query tuning.
Firstly, I wanted to know whether it is expectedormal to require 1.3 seconds for 10000 stored procedure calls. If general result is that it takes lesser amount of time, I wanted to know how can I improve.
The empty stored procedure I tried had no parameters and it returned hard coded -1.
Thanks for your help in advance,
-Neel.
Perhaps if you told us what the code is supposed to do 10,000 times in a second, we may be able to come up with a workaround.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2010 at 3:39 am
Thanks for the replies.
Set based operations - yes, that was the first thought that came to my mind as well and in fact I suggested it. But, unfortunately, for business reasons, it was turned down.
Here is the structure:
--DROP TABLE [dbo].[ATable];
CREATE TABLE
[dbo].[ATable]
(
[Date_Created] datetime NOT NULL CONSTRAINT [ATable_Date_Created] DEFAULT (GETDATE()),
[Date_Modified] datetime NULL,
[A_GUID_Column] uniqueidentifier NOT NULL,
[A_Varchar_Column] varchar(50) NOT NULL,
[Another_Varchar_Column] varchar(50) NOT NULL,
CONSTRAINT [ATable_pk] PRIMARY KEY CLUSTERED
(
[A_GUID_Column],[A_Varchar_Column]
)
ON [PRIMARY]
)
ON [PRIMARY]
--DROP TABLE ForeignKeyTable;
CREATE TABLE
[dbo].[ForeignKeyTable]
(
[Record_Number] bigint IDENTITY(1,1) NOT NULL,
[A_GUID_Column] UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() ROWGUIDCOL NOT NULL,
[Date_Created] datetime NOT NULL CONSTRAINT [df_ForeignKeyTable_Date_Created] DEFAULT (GETDATE()),
[Date_Modified] datetime NULL,
[Some_Other_GUID_Column] uniqueidentifier NOT NULL,
[An_Int_Column] int NOT NULL CONSTRAINT [df_ForeignKeyTable_An_Int_Column] DEFAULT (1),
CONSTRAINT [pk_ForeignKeyTable] PRIMARY KEY CLUSTERED ([Record_Number]) ON [PRIMARY],
CONSTRAINT [ix_ForeignKeyTable] UNIQUE NONCLUSTERED ([A_GUID_Column]) ON [PRIMARY]
)
ON [PRIMARY]
--DROP fk_ATable_ForeignKeyTable;
ALTER TABLE
[dbo].[ATable]
ADD CONSTRAINT [fk_ATable_ForeignKeyTable]
FOREIGN KEY ([A_GUID_Column])
REFERENCES [dbo].[ForeignKeyTable] ([A_GUID_Column])
GO
/* Can have at the most 5 million records */
CREATE VIEW ATable_View AS
SELECT * FROM ATable WHERE A_GUID_Column = (SELECT A_GUID_Column FROM ForeignKeyTable WHERE An_Int_Column = 2)
GO
/*Please note I am calling this procedure from C# code*/
CREATE PROCEDURE [dbo].[Find_Value]
@A_Varchar_Value varchar(50),
@Another_Varchar_Value varchar(50) OUTPUT
AS
SET @Another_Varchar_Value =
(SELECT Another_Varchar_Column FROM ATable_View
WHERE A_Varchar_Column=@A_Varchar_Value)
GO
May 16, 2010 at 6:59 am
I would add a "SET NOCOUNT ON;" to the top of the procedure to suppress DONE_IN_PROC messages.
Also, can we see the C# code to call the procedure please?
I should point out that I am in the this-is-a-dumb-idea camp, but I'm willing to answer the question at face value at the same time.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 16, 2010 at 8:13 am
That didn't make any difference. Mainly, I am not certain it would make difference to the time taken for *inter process communication* between my C# app and SQL Server. But thanks for the reply - it would be helpful when it comes to tuning stored procedure itself I believe.
I will post the code in short while.
Thanks,
-Neel.
May 16, 2010 at 10:03 am
neelsmail (5/16/2010)
That didn't make any difference. Mainly, I am not certain it would make difference to the time taken for *inter process communication* between my C# app and SQL Server.
Adding to the size of data sent never improves performance, regardless of whether it runs locally or over the network 🙂
Anyway, so in your test on the empty procedure, you achieved 10,000 procedure calls in 1.3s
That's an average of 0.13ms per call, which isn't too bad I would say.
You should try different protocols though - Shared Memory is not always fastest (believe it or not).
It will be interesting to see whether the calling code is single- or multi-threaded. Is the computer you are running the test on multi-core? You should also bear in mind that testing with Express Edition is not necessarily the best choice - Developer Edition is very cheap and performs exactly as Enterprise Edition does.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 16, 2010 at 11:12 am
neelsmail (5/16/2010)
Thanks for the replies.Set based operations - yes, that was the first thought that came to my mind as well and in fact I suggested it. But, unfortunately, for business reasons, it was turned down.
Here is the structure:
--DROP TABLE [dbo].[ATable];
CREATE TABLE
[dbo].[ATable]
(
[Date_Created] datetime NOT NULL CONSTRAINT [ATable_Date_Created] DEFAULT (GETDATE()),
[Date_Modified] datetime NULL,
[A_GUID_Column] uniqueidentifier NOT NULL,
[A_Varchar_Column] varchar(50) NOT NULL,
[Another_Varchar_Column] varchar(50) NOT NULL,
CONSTRAINT [ATable_pk] PRIMARY KEY CLUSTERED
(
[A_GUID_Column],[A_Varchar_Column]
)
ON [PRIMARY]
)
ON [PRIMARY]
--DROP TABLE ForeignKeyTable;
CREATE TABLE
[dbo].[ForeignKeyTable]
(
[Record_Number] bigint IDENTITY(1,1) NOT NULL,
[A_GUID_Column] UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() ROWGUIDCOL NOT NULL,
[Date_Created] datetime NOT NULL CONSTRAINT [df_ForeignKeyTable_Date_Created] DEFAULT (GETDATE()),
[Date_Modified] datetime NULL,
[Some_Other_GUID_Column] uniqueidentifier NOT NULL,
[An_Int_Column] int NOT NULL CONSTRAINT [df_ForeignKeyTable_An_Int_Column] DEFAULT (1),
CONSTRAINT [pk_ForeignKeyTable] PRIMARY KEY CLUSTERED ([Record_Number]) ON [PRIMARY],
CONSTRAINT [ix_ForeignKeyTable] UNIQUE NONCLUSTERED ([A_GUID_Column]) ON [PRIMARY]
)
ON [PRIMARY]
--DROP fk_ATable_ForeignKeyTable;
ALTER TABLE
[dbo].[ATable]
ADD CONSTRAINT [fk_ATable_ForeignKeyTable]
FOREIGN KEY ([A_GUID_Column])
REFERENCES [dbo].[ForeignKeyTable] ([A_GUID_Column])
GO
/* Can have at the most 5 million records */
CREATE VIEW ATable_View AS
SELECT * FROM ATable WHERE A_GUID_Column = (SELECT A_GUID_Column FROM ForeignKeyTable WHERE An_Int_Column = 2)
GO
/*Please note I am calling this procedure from C# code*/
CREATE PROCEDURE [dbo].[Find_Value]
@A_Varchar_Value varchar(50),
@Another_Varchar_Value varchar(50) OUTPUT
AS
SET @Another_Varchar_Value =
(SELECT Another_Varchar_Column FROM ATable_View
WHERE A_Varchar_Column=@A_Varchar_Value)
GO
You still haven't explained what you're trying to do 10,000 times a second...
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2010 at 11:39 am
> You still haven't explained what you're trying to do 10,000 times a second...
I thought I did.. but perhaps I didn't understand your question.
I am trying to call the Find() procedure 10,000 times a second. I cannot use set operations because it has been turned down for business reasons. Clients of C# app would need to have this information and VERY urgently. The only way I can see this happening is either I cache the information, which I don't want to do since view can have 5 million records or try to have super-fast inter-process communication.
As Paul mentioned, yes, I believe 1.3 seconds for 10000 calls good performance but unfortunately, that doesn't work for me.
I am still confused about your question a bit. If the explanation above doesn't provide you with enough information, could you explain what exactly you are looking for?
Thanks,
-Neel.
May 16, 2010 at 12:04 pm
neelsmail (5/16/2010)
> You still haven't explained what you're trying to do 10,000 times a second...I thought I did.. but perhaps I didn't understand your question.
I am trying to call the Find() procedure 10,000 times a second. I cannot use set operations because it has been turned down for business reasons. Clients of C# app would need to have this information and VERY urgently. The only way I can see this happening is either I cache the information, which I don't want to do since view can have 5 million records or try to have super-fast inter-process communication.
As Paul mentioned, yes, I believe 1.3 seconds for 10000 calls good performance but unfortunately, that doesn't work for me.
I am still confused about your question a bit. If the explanation above doesn't provide you with enough information, could you explain what exactly you are looking for?
Thanks,
-Neel.
Thanks for the feedback and now I understand. I believe there are some optimizations that could be made but there's just one thing left to figure out... do the 10,000 calls occur sequentially by one user or will they occur in parallel by more than one user? If it is just one user making 10,000 calls, I guess I still don't understand the business reason for why that must be so. For example, 10000 "things" could be passed as a single delimited string and split on the server side very quickly. From that, a single result set could be returned and it would all happen very quickly... I can't test in your environment but I don't see why it couldn't happen in less than a second provided that the network latency in the system isn't the real problem.
Also (still trying to figure a way around it all for you), what are the business reasons why this can't be done in a set based fashion? Also, why can't this information be cached on the Web Server... after all, it IS only 5 million rows.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 16, 2010 at 12:10 pm
neelsmail (5/16/2010)
> You still haven't explained what you're trying to do 10,000 times a second...I thought I did.. but perhaps I didn't understand your question.
I am trying to call the Find() procedure 10,000 times a second. I cannot use set operations because it has been turned down for business reasons. Clients of C# app would need to have this information and VERY urgently. The only way I can see this happening is either I cache the information, which I don't want to do since view can have 5 million records or try to have super-fast inter-process communication.
As Paul mentioned, yes, I believe 1.3 seconds for 10000 calls good performance but unfortunately, that doesn't work for me.
I am still confused about your question a bit. If the explanation above doesn't provide you with enough information, could you explain what exactly you are looking for?
Thanks,
-Neel.
There are several things I don't understand in your post:
1) Who on earth would have made a decision to "turn down set operations"? Would you mind get this person involved in this thread and explain in detail the reason for doing so? It's like buying a Ferrari and lock it down to first gear for "business reasons".
2) If you have an issue that's "VERY urgently" you might want to get a consultant in. This person most probably will be question the strange business requirements as well and could help you resolving it better than a forum can.
3) If you refuse to use fast processing methods you will have to live with the downsides of it... [sarcasm on] Or you could recommend purchasing bigger hardware [sarcasm off]
Again, get a consultant in to help you resolving the minor issue with your function as well as the major issue with the business requirements you're faced with!
May 16, 2010 at 12:11 pm
that was wierd.....it quoted wrong....
Have you tried calling the stored proc 10,000 times purely in SQL server to see whether that is a limiting factor?
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);May 17, 2010 at 1:40 am
> do the 10,000 calls occur sequentially by one user or will they occur in parallel by more than one user?
It may or may not depending on how many clients there are.
> For example, 10000 "things" could be passed as a single delimited string and split on the server side very quickly.
Yes, I thought that too but you can't fight business reasons 🙂
> after all, it IS only 5 million rows.
I tried caching in C# app I am writing - mem usage goes to 600 MB with two columns - both varchar(50)
> ...I believe there are some optimizations that could be made...
Could you let me know what those optimizations can be?
Thanks for your patience and replies,
-Neel.
May 17, 2010 at 1:41 am
> Have you tried calling the stored proc 10,000 times purely in SQL server to see whether that is a limiting factor?
I tried this in SQL Server Management studio and results are completed dependent on number or records and all which I believe is time taken to run stored procedure and not cross process communication:
DECLARE @a_varchar_value_input varchar(50)
DECLARE @a_varchar_value_output varchar(50)
DECLARE @counter INT
SET @counter = 0
DECLARE @StartTime datetime
SET @StartTime = GETDATE()
WHILE (@counter < 10000)
BEGIN
SET @a_varchar_value_input =
'lookupvalue' + CONVERT(varchar(15), @counter )
EXECUTE Find_Value
@a_varchar_value_input,
@a_varchar_value_output OUTPUT
--PRINT @a_varchar_value_input + ':' + @a_varchar_value_output
SET @counter = @counter + 1
END
SELECT ExecutionTimeInMS = DATEDIFF(millisecond, @StartTime, getdate())
May 17, 2010 at 7:23 am
neelsmail (5/17/2010)
> do the 10,000 calls occur sequentially by one user or will they occur in parallel by more than one user?It may or may not depending on how many clients there are.
> For example, 10000 "things" could be passed as a single delimited string and split on the server side very quickly.
Yes, I thought that too but you can't fight business reasons 🙂
> after all, it IS only 5 million rows.
I tried caching in C# app I am writing - mem usage goes to 600 MB with two columns - both varchar(50)
> ...I believe there are some optimizations that could be made...
Could you let me know what those optimizations can be?
Thanks for your patience and replies,
-Neel.
Heh... you can always fight the business reasons if something can't be made to work. 😉
The requirement is 10000 finds per second, right? Setup 5 or 10 connections to find 2000 or 1000 different items and have them all light off at the same time. You'll hit your mark.
An optimization you should probably try is getting rid of the view... the view has a SELECT * in it while your procedure appears to be returning only one column. That means you're moving data that doesn't need to be moved. Return only the data that needs to be returned.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply