January 30, 2017 at 2:54 pm
I have a logic in my Stored procedure ,where I want to check the timings of min,max and avg timings.
If (@ID like 'TRE%')
BEGIN
SELECT
@Col1= substring(blabla),
@col2 = substring(blabla),
@col3 = substring(blabla)
END
ELSE IF (@SDF != NULL)
BEGIN
SELECT
@Col1= substring(blabla),
@col2 = substring(blabla),
@col3 = substring(blabla)
END
January 30, 2017 at 3:02 pm
MIN(), MAX(), and AVG() are properties of sets. You don't have any sets here.
Also, blabla is not a valid argument. If it's a string, it needs to be quoted. If it's a field in a table, then you need to reference the table.
Oh, and SUBSTRING() requires three arguments. You've only supplied one.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 30, 2017 at 3:10 pm
drew.allen - Monday, January 30, 2017 3:02 PMMIN(), MAX(), and AVG() are properties of sets. You don't have any sets here.Also, blabla is not a valid argument. If it's a string, it needs to be quoted. If it's a field in a table, then you need to reference the table.
Drew
This is the function (Table valued)' ' where I am calling in SP, The above code is just to test I have pasted separately from the function.
SELECT @col1= col1,
@col2 = col2,
@Col3 = col3
from [dbo].[Employee](@ID,@SDF)
January 30, 2017 at 3:30 pm
mcfarlandparkway - Monday, January 30, 2017 3:10 PMdrew.allen - Monday, January 30, 2017 3:02 PMMIN(), MAX(), and AVG() are properties of sets. You don't have any sets here.Also, blabla is not a valid argument. If it's a string, it needs to be quoted. If it's a field in a table, then you need to reference the table.
Drew
This is the function (Table valued)' ' where I am calling in SP, The above code is just to test I have pasted separately from the function.
SELECT @col1= col1,
@col2 = col2,
@Col3 = col3
from [dbo].[Employee](@ID,@SDF)
You have to remember that we cannot see what you see. We can only see what you give us.
Forum etiquette requests that you post sample data (missing from your question) and expected results (also missing). For more information, follow the link in my signature.
Without that, all we can do is tell you to look at the MIN (Transact SQL), MAX (Transact SQL), and AVG (Transact SQL) functions.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 30, 2017 at 3:41 pm
drew.allen - Monday, January 30, 2017 3:30 PMmcfarlandparkway - Monday, January 30, 2017 3:10 PMdrew.allen - Monday, January 30, 2017 3:02 PMMIN(), MAX(), and AVG() are properties of sets. You don't have any sets here.Also, blabla is not a valid argument. If it's a string, it needs to be quoted. If it's a field in a table, then you need to reference the table.
Drew
This is the function (Table valued)' ' where I am calling in SP, The above code is just to test I have pasted separately from the function.
SELECT @col1= col1,
@col2 = col2,
@Col3 = col3
from [dbo].[Employee](@ID,@SDF)You have to remember that we cannot see what you see. We can only see what you give us.
Forum etiquette requests that you post sample data (missing from your question) and expected results (also missing). For more information, follow the link in my signature.
Without that, all we can do is tell you to look at the MIN (Transact SQL), MAX (Transact SQL), and AVG (Transact SQL) functions.
Drew
The problem is that i have created a temp table to track the performance like columns descr and start time
i have placed this insert statement before calling the function and after this i am updating the temp table with end date
so this gives the start and end time of this logic.
Now i am retreving 300k records from table and i dont want to insert each and every rec in temp table to track the timings
so instead i am trying to find a way to see can i get min time,max time and Avg time of this function logic with out inserting into temp table.
January 30, 2017 at 4:00 pm
mcfarlandparkway - Monday, January 30, 2017 3:41 PMdrew.allen - Monday, January 30, 2017 3:30 PMmcfarlandparkway - Monday, January 30, 2017 3:10 PMdrew.allen - Monday, January 30, 2017 3:02 PMMIN(), MAX(), and AVG() are properties of sets. You don't have any sets here.Also, blabla is not a valid argument. If it's a string, it needs to be quoted. If it's a field in a table, then you need to reference the table.
Drew
This is the function (Table valued)' ' where I am calling in SP, The above code is just to test I have pasted separately from the function.
SELECT @col1= col1,
@col2 = col2,
@Col3 = col3
from [dbo].[Employee](@ID,@SDF)You have to remember that we cannot see what you see. We can only see what you give us.
Forum etiquette requests that you post sample data (missing from your question) and expected results (also missing). For more information, follow the link in my signature.
Without that, all we can do is tell you to look at the MIN (Transact SQL), MAX (Transact SQL), and AVG (Transact SQL) functions.
Drew
The problem is that i have created a temp table to track the performance like columns descr and start time
i have placed this insert statement before calling the function and after this i am updating the temp table with end date
so this gives the start and end time of this logic.Now i am retreving 300k records from table and i dont want to insert each and every rec in temp table to track the timings
so instead i am trying to find a way to see can i get min time,max time and Avg time of this function logic with out inserting into temp table.
If you're tracking performance of your UDF, you do not need to insert each and every of your 300K records into the temp table. You only need to insert the duration. In fact, when I am doing performance testing, I just insert the current time (usually SYSDATETIME) into a table and then use LAG to calculate the duration based on the difference between the current time and the previous time at the appropriate scale.
There is absolutely no reason not to use the already defined MIN(), MAX(), and AVG() functions.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 30, 2017 at 4:18 pm
drew.allen - Monday, January 30, 2017 4:00 PMmcfarlandparkway - Monday, January 30, 2017 3:41 PMdrew.allen - Monday, January 30, 2017 3:30 PMmcfarlandparkway - Monday, January 30, 2017 3:10 PMdrew.allen - Monday, January 30, 2017 3:02 PMMIN(), MAX(), and AVG() are properties of sets. You don't have any sets here.Also, blabla is not a valid argument. If it's a string, it needs to be quoted. If it's a field in a table, then you need to reference the table.
Drew
This is the function (Table valued)' ' where I am calling in SP, The above code is just to test I have pasted separately from the function.
SELECT @col1= col1,
@col2 = col2,
@Col3 = col3
from [dbo].[Employee](@ID,@SDF)You have to remember that we cannot see what you see. We can only see what you give us.
Forum etiquette requests that you post sample data (missing from your question) and expected results (also missing). For more information, follow the link in my signature.
Without that, all we can do is tell you to look at the MIN (Transact SQL), MAX (Transact SQL), and AVG (Transact SQL) functions.
Drew
The problem is that i have created a temp table to track the performance like columns descr and start time
i have placed this insert statement before calling the function and after this i am updating the temp table with end date
so this gives the start and end time of this logic.Now i am retreving 300k records from table and i dont want to insert each and every rec in temp table to track the timings
so instead i am trying to find a way to see can i get min time,max time and Avg time of this function logic with out inserting into temp table.
If you're tracking performance of your UDF, you do not need to insert each and every of your 300K records into the temp table. You only need to insert the duration. In fact, when I am doing performance testing, I just insert the current time (usually SYSDATETIME) into a table and then use LAG to calculate the duration based on the difference between the current time and the previous time at the appropriate scale.
There is absolutely no reason not to use the already defined MIN(), MAX(), and AVG() functions.
Drew
So before this function you want me to keep sysdatetime ( current time)? Can you show me to how to track current time and end time
suppose i am getting 100k rec from table and after immediately calling this function.
when I execute this Sp i want to see the timings of this logic for 100k. so how to modify the code here?
SELECT @col1= col1,
@col2 = col2,
@Col3 = col3
from [dbo].[Employee](@ID,@SDF)
January 30, 2017 at 4:33 pm
mcfarlandparkway - Monday, January 30, 2017 4:18 PMdrew.allen - Monday, January 30, 2017 4:00 PMmcfarlandparkway - Monday, January 30, 2017 3:41 PMdrew.allen - Monday, January 30, 2017 3:30 PMmcfarlandparkway - Monday, January 30, 2017 3:10 PMdrew.allen - Monday, January 30, 2017 3:02 PMMIN(), MAX(), and AVG() are properties of sets. You don't have any sets here.Also, blabla is not a valid argument. If it's a string, it needs to be quoted. If it's a field in a table, then you need to reference the table.
Drew
This is the function (Table valued)' ' where I am calling in SP, The above code is just to test I have pasted separately from the function.
SELECT @col1= col1,
@col2 = col2,
@Col3 = col3
from [dbo].[Employee](@ID,@SDF)You have to remember that we cannot see what you see. We can only see what you give us.
Forum etiquette requests that you post sample data (missing from your question) and expected results (also missing). For more information, follow the link in my signature.
Without that, all we can do is tell you to look at the MIN (Transact SQL), MAX (Transact SQL), and AVG (Transact SQL) functions.
Drew
The problem is that i have created a temp table to track the performance like columns descr and start time
i have placed this insert statement before calling the function and after this i am updating the temp table with end date
so this gives the start and end time of this logic.Now i am retreving 300k records from table and i dont want to insert each and every rec in temp table to track the timings
so instead i am trying to find a way to see can i get min time,max time and Avg time of this function logic with out inserting into temp table.
If you're tracking performance of your UDF, you do not need to insert each and every of your 300K records into the temp table. You only need to insert the duration. In fact, when I am doing performance testing, I just insert the current time (usually SYSDATETIME) into a table and then use LAG to calculate the duration based on the difference between the current time and the previous time at the appropriate scale.
There is absolutely no reason not to use the already defined MIN(), MAX(), and AVG() functions.
Drew
So before this function you want me to keep sysdatetime ( current time)? Can you show me to how to track current time and end time
suppose i am getting 100k rec from table and after immediately calling this function.
when I execute this Sp i want to see the timings of this logic for 100k. so how to modify the code here?
SELECT @col1= col1,
@col2 = col2,
@Col3 = col3
from [dbo].[Employee](@ID,@SDF)
I'm reading along and I'm confused. Don't you need to calculate a difference between @startTime and @endTime and then you can do MIN, MAX, AVG. You could then add that to your start time, but I don't see that anywhere. Shouldn't there be a DATEDIFF in there somewhere?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply