October 28, 2010 at 3:33 pm
timing tests are on my desktop
October 28, 2010 at 3:36 pm
Roy Ernest (10/28/2010)
Check the execution plan and see if there is any difference when executing as RAW SQL statements and when executing as TVF.Temp tables can be used depending on the circumstances. But you are using a Variable table and that can cause performance issues if there lots of rows in it.
Indexes should be created on the underlying tables to make the execution faster. Heap tables are not the right way to go.
In this case there about 2000 ish or so rows. We are not sure how to do indexing, we have hundred plus tables at this point that are not indexed. We are a mostly self taught group that builds asp web pages for our companies intranet and have recently started to use SQL more then just data storage.
October 28, 2010 at 3:38 pm
mbender,
I see one thing in the TVF - you're calling another function to get the first day of the month. I don't know how you're doing that, but this is the best (fastest, most scalable) way:
SET @MonthDate = DateAdd(month, DateDiff(month, 0, @MonthDate), 0);
You only need all of this in a TVF if you are going to be selecting from it, and it is more complicated than a view. Since it's going to an ASP page, you would probably get a lot better performance putting it into a stored procedure. There, you can use a temporary table with appropriate indexes, and the optimizer will give a better plan for you (it sees a table variable as only having one record).
However, your TVF does look kinda, well, "messy". So, I'm going to ask you to help us help you make it better. Start off by reading the first link in my signature. Then, post the table definition (DDL) and some insert statements to fill the tables with sample data. Finally, and based off of the sample data provided, what your expected results should be. If you do this, several of the folks here will jump on this to help you. (And please ensure that the code you post will actually run - you would be surprised at how much doesn't!)
Oh - when you're composing your post, you will see to the left of the edit window a box titled "IFCode Shortcuts". In that list is one that says [ code = "sql" ][ / code] (without the spaces, I put them in so the editor won't try to make everything else here behave like code). Please put your code between those - it makes it a lot easier for us to copy/paste the code into our own query editor window, so that we can give you a tested response back. Edit: If you click the quote button on my message, you'll see how it looks for the code that I posted.
So, if you help us, we'll not only help you, but teach you also. Are you up for it?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 28, 2010 at 3:48 pm
mbender (10/28/2010)
The reason i'm using a TVF is ya i need it to be in a table format. I'm trying to pull the data live, rather then storing it in a table. The destination for the data is an asp page. I'm open to any ideas. It just was odd that the execution went longer when putting it TVF, but again that's probably my in experience.Thanks
As Wayne has already said, your best bet is to put this into a stored procedure and obtain the table result from that. 2000 rows seems large for an asp page, is this winnowed further before display?
You're using the code
DATEADD(m,-1,@MonthDate)
5 times. If you make this another variable at the start
DECLARE @EarlierMonth date = DATEADD(m,-1,@MonthDate)
or DECLARE @EarlierMonth datetime
SET @EarlierMonth = DATEADD(m,-1,@MonthDate)
in earlier versions of SQL Server, then you only have to process the calculation once.
I haven't used Merge Into yet, so I'll be reading up on that and be back. It looks like you're using it to see which rows have earlier month data as well and are using Merge Into to sum the values for those rows. Is that correct?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 28, 2010 at 3:57 pm
Stefan Krzywicki (10/28/2010)
mbender (10/28/2010)
The reason i'm using a TVF is ya i need it to be in a table format. I'm trying to pull the data live, rather then storing it in a table. The destination for the data is an asp page. I'm open to any ideas. It just was odd that the execution went longer when putting it TVF, but again that's probably my in experience.Thanks
As Wayne has already said, your best bet is to put this into a stored procedure and obtain the table result from that. 2000 rows seems large for an asp page, is this winnowed further before display?
You're using the code
DATEADD(m,-1,@MonthDate)5 times. If you make this another variable at the start
DECLARE @EarlierMonth date = DATEADD(m,-1,@MonthDate)or
DECLARE @EarlierMonth datetimeSET @EarlierMonth = DATEADD(m,-1,@MonthDate)
in earlier versions of SQL Server, then you only have to process the calculation once.
I haven't used Merge Into yet, so I'll be reading up on that and be back. It looks like you're using it to see which rows have earlier month data as well and are using Merge Into to sum the values for those rows. Is that correct?
Ya i'm using merge to basically compare the tables and if there is a match update the fields or if there is not insert to fill in what i'm missing. I'm trying to show two different months of data side by side with the variances between them.
October 28, 2010 at 3:58 pm
WayneS (10/28/2010)
mbender,I see one thing in the TVF - you're calling another function to get the first day of the month. I don't know how you're doing that, but this is the best (fastest, most scalable) way:
SET @MonthDate = DateAdd(month, DateDiff(month, 0, @MonthDate), 0);You only need all of this in a TVF if you are going to be selecting from it, and it is more complicated than a view. Since it's going to an ASP page, you would probably get a lot better performance putting it into a stored procedure. There, you can use a temporary table with appropriate indexes, and the optimizer will give a better plan for you (it sees a table variable as only having one record).
However, your TVF does look kinda, well, "messy". So, I'm going to ask you to help us help you make it better. Start off by reading the first link in my signature. Then, post the table definition (DDL) and some insert statements to fill the tables with sample data. Finally, and based off of the sample data provided, what your expected results should be. If you do this, several of the folks here will jump on this to help you. (And please ensure that the code you post will actually run - you would be surprised at how much doesn't!)
Oh - when you're composing your post, you will see to the left of the edit window a box titled "IFCode Shortcuts". In that list is one that says [ code = "sql" ][ / code] (without the spaces, I put them in so the editor won't try to make everything else here behave like code). Please put your code between those - it makes it a lot easier for us to copy/paste the code into our own query editor window, so that we can give you a tested response back. Edit: If you click the quote button on my message, you'll see how it looks for the code that I posted.
So, if you help us, we'll not only help you, but teach you also. Are you up for it?
I am certainly up for it and will have that for you tomorrow, thank you all for your help.
October 28, 2010 at 3:59 pm
Stefan Krzywicki (10/28/2010)
mbender (10/28/2010)
The reason i'm using a TVF is ya i need it to be in a table format. I'm trying to pull the data live, rather then storing it in a table. The destination for the data is an asp page. I'm open to any ideas. It just was odd that the execution went longer when putting it TVF, but again that's probably my in experience.Thanks
As Wayne has already said, your best bet is to put this into a stored procedure and obtain the table result from that. 2000 rows seems large for an asp page, is this winnowed further before display?
You're using the code
DATEADD(m,-1,@MonthDate)5 times. If you make this another variable at the start
DECLARE @EarlierMonth date = DATEADD(m,-1,@MonthDate)or
DECLARE @EarlierMonth datetimeSET @EarlierMonth = DATEADD(m,-1,@MonthDate)
in earlier versions of SQL Server, then you only have to process the calculation once.
I haven't used Merge Into yet, so I'll be reading up on that and be back. It looks like you're using it to see which rows have earlier month data as well and are using Merge Into to sum the values for those rows. Is that correct?
When i put this in a stored procedure it took the same amount of time as the TVF.
October 28, 2010 at 4:11 pm
mbender (10/28/2010)
Stefan Krzywicki (10/28/2010)
mbender (10/28/2010)
The reason i'm using a TVF is ya i need it to be in a table format. I'm trying to pull the data live, rather then storing it in a table. The destination for the data is an asp page. I'm open to any ideas. It just was odd that the execution went longer when putting it TVF, but again that's probably my in experience.Thanks
As Wayne has already said, your best bet is to put this into a stored procedure and obtain the table result from that. 2000 rows seems large for an asp page, is this winnowed further before display?
You're using the code
DATEADD(m,-1,@MonthDate)5 times. If you make this another variable at the start
DECLARE @EarlierMonth date = DATEADD(m,-1,@MonthDate)or
DECLARE @EarlierMonth datetimeSET @EarlierMonth = DATEADD(m,-1,@MonthDate)
in earlier versions of SQL Server, then you only have to process the calculation once.
I haven't used Merge Into yet, so I'll be reading up on that and be back. It looks like you're using it to see which rows have earlier month data as well and are using Merge Into to sum the values for those rows. Is that correct?
When i put this in a stored procedure it took the same amount of time as the TVF.
Yep, not surprised. There's more that needs to be done, but I figure if we strip away complexity little by little you'll end up with what you need and nothing extra.
I noticed that the previous month doesn't include customer name. Does this mean you'll never get a previous month if there is no current month record?
Also, how are you recording how long the process is taking?
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 28, 2010 at 6:41 pm
I have couple of suggestions, but you have to take the first step on that. Put the Table structure here. We could help you with the indexing.
You also said that the @tempTable (Variable table) will get around 2000 rows. That could cause degradation in performance in my humble opinion. In this case, maybe you should look at Temporary tables.
Another question, You said these rows are handled in the asp page. How are you retrieving the data and writing it? Are you using a vbarray? That is much more effiecient since you can just get all the record in one go into the array and then you can close your connection to the DB. Otherwise you have to open the dataset and do it row by row. That means your connection is still alive in the database.
When you were doing the test, you ran the sql statement before makingit into a function in SSMS, right? How did you test your function? In SSMS itself?
-Roy
October 28, 2010 at 6:47 pm
CELKO (10/28/2010)
I tried to read what you posted got 45 minutes. You have re-discovered the same algorithms I used on mag tape files decades ago. Your temp tables are scratch tapes. You do things one step at time,rewind the tape and make another pass over the Master file.You use BIT flags as if we were still in Assembly language.
You use MONEY in spite of the math problems it has. But it looks like a formatted PIC field declaration in COBOL.
You put the meta data term "_key" in column names.
You write:
AND BBD.bia_start_date <= @in_something_month
AND BBD.bia_end_date >= @in_something_month
instead of:
@in_something_month BETWEEN BBD.bia_start_date
AND BBD.bia_end_date
You call a function to get the first day of a month, and it name violates ISO-11179 naming rules-- drop the silly "ufn-" prefix.
SET @in_something_month = dbo.ufn_GetFirstDayOfMonth(@in_something_month);
instead of writing an expression the optimizer could use.
This is not SQL. It is COBOL and tape files written in SQL. This is a bigger problem than a Forum can handle. Sorry.
Joe,
Just for your information this is a forum for newbies. Why don't you drop this arrogant, condescending attitude of yours and start offer some actual help.
October 28, 2010 at 7:38 pm
Roy Ernest (10/28/2010)
I have couple of suggestions, but you have to take the first step on that. Put the Table structure here. We could help you with the indexing.You also said that the @tempTable (Variable table) will get around 2000 rows. That could cause degradation in performance in my humble opinion. In this case, maybe you should look at Temporary tables.
Another question, You said these rows are handled in the asp page. How are you retrieving the data and writing it? Are you using a vbarray? That is much more effiecient since you can just get all the record in one go into the array and then you can close your connection to the DB. Otherwise you have to open the dataset and do it row by row. That means your connection is still alive in the database.
When you were doing the test, you ran the sql statement before makingit into a function in SSMS, right? How did you test your function? In SSMS itself?
I'm hoping he can get rid of the temp variable/table altogether.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 28, 2010 at 7:46 pm
Stefan Krzywicki (10/28/2010)
Roy Ernest (10/28/2010)
I have couple of suggestions, but you have to take the first step on that. Put the Table structure here. We could help you with the indexing.You also said that the @tempTable (Variable table) will get around 2000 rows. That could cause degradation in performance in my humble opinion. In this case, maybe you should look at Temporary tables.
Another question, You said these rows are handled in the asp page. How are you retrieving the data and writing it? Are you using a vbarray? That is much more effiecient since you can just get all the record in one go into the array and then you can close your connection to the DB. Otherwise you have to open the dataset and do it row by row. That means your connection is still alive in the database.
When you were doing the test, you ran the sql statement before makingit into a function in SSMS, right? How did you test your function? In SSMS itself?
I'm hoping he can get rid of the temp variable/table altogether.
The problem is that while he's using a TVF, he'll have to use table variables.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 28, 2010 at 7:50 pm
WayneS (10/28/2010)
Stefan Krzywicki (10/28/2010)
Roy Ernest (10/28/2010)
I have couple of suggestions, but you have to take the first step on that. Put the Table structure here. We could help you with the indexing.You also said that the @tempTable (Variable table) will get around 2000 rows. That could cause degradation in performance in my humble opinion. In this case, maybe you should look at Temporary tables.
Another question, You said these rows are handled in the asp page. How are you retrieving the data and writing it? Are you using a vbarray? That is much more effiecient since you can just get all the record in one go into the array and then you can close your connection to the DB. Otherwise you have to open the dataset and do it row by row. That means your connection is still alive in the database.
When you were doing the test, you ran the sql statement before makingit into a function in SSMS, right? How did you test your function? In SSMS itself?
I'm hoping he can get rid of the temp variable/table altogether.
The problem is that while he's using a TVF, he'll have to use table variables.
Actually, once he shows us the DDL for the base tables, provides some sample data, and expected results; we should be able to provide a much simpler approach to the problem. Looking at the code doesn't help enough to know what he is trying to accomplish.
Note to OP, in your sample data be sure to provide some ata outside the date range being reported as well as data that only falls in one of the reporting months.
October 28, 2010 at 7:52 pm
WayneS (10/28/2010)
Stefan Krzywicki (10/28/2010)
Roy Ernest (10/28/2010)
I have couple of suggestions, but you have to take the first step on that. Put the Table structure here. We could help you with the indexing.You also said that the @tempTable (Variable table) will get around 2000 rows. That could cause degradation in performance in my humble opinion. In this case, maybe you should look at Temporary tables.
Another question, You said these rows are handled in the asp page. How are you retrieving the data and writing it? Are you using a vbarray? That is much more effiecient since you can just get all the record in one go into the array and then you can close your connection to the DB. Otherwise you have to open the dataset and do it row by row. That means your connection is still alive in the database.
When you were doing the test, you ran the sql statement before makingit into a function in SSMS, right? How did you test your function? In SSMS itself?
I'm hoping he can get rid of the temp variable/table altogether.
The problem is that while he's using a TVF, he'll have to use table variables.
Right, but since he's just getting a dataset for use in an asp page, he doesn't need to use a TVF. In fact, he's already ported the code to an SP and we can work from there.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
October 29, 2010 at 7:20 am
I could be wrong, but it looks to me like this could be turned into a single, moderately complex, query. I'd need table definitions and some sample data to help with that, but I think it could be done. If so, it'll definitely be faster to use that than to use a UDF with table variables.
One thing I see in the discussion here, if I'm not mistaken, is a confusion between temp tables and table variables. Temp tables are created just like regular tables, but the name starts with "#", while table variables are declared (instead of created) and start with "@".
Temp tables can be indexed, and keep statistics, and thus are often better than table variables, for this kind of thing. You can't use them in a UDF, but you can in a proc.
To answer the original question, yes, wrapping a script into a UDF does usually cause a performance hit all by itself. But, it's usually in terms of about a 3-5% increase in runtime, not the huge increase you're running into. Does the UDF take as long to run the second time as it does the first time?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply