April 26, 2009 at 12:05 pm
Hey folks ;-).
I need you guys opinion on what would be advantage of the following code? I don't see any, to me it is doing SQL's job....
DECLARE @rowData TABLE
(MainTableID INT,
PageName VARCHAR(50),
PageData1 VARCHAR(50),
PageData2 VARCHAR(50),
PageTypeID INT,
PageType VARCHAR(50))
INSERT INTO @rowData
SELECT main.MainTableID,
main.PageName,
main.PageData1,
main.PageData2,
ptype.PageTypeID,
ptype.Description AS PageType
FROM ( SELECT M.MainTableID, M.PageName, S.PageData1, S.PageData2, M.PageTypeID
FROM MainTable AS M
INNER JOIN SubTable AS S ON M.MainTableID = S.MainTableID) AS main
LEFT JOIN PageTypeTable AS ptype ON main.PageTypeID = ptype.PageTypeID
DECLARE @FilteredData TABLE
(MainTableID INT PRIMARY KEY)
INSERT INTO @FilteredData
SELECT DISTINCT MainTableID
FROM @rowData AS R
WHERE R.PageTypeID = 89
SELECT *
FROM @rowData AS R
WHERE R.MainTableID IN (SELECT MainTableID
FROM @FilteredData)
The MainTable, as name suggest holds the main data for the database. This chuck of code is executed EVERY time someone access a page. It is dynamically built with in the application and then executed on SQL Server. Passes in some variables to check against the Page Type and returns usually only one record. What annoys me is it is scanning a full table EVERYTIME, and these tables have in some cases over 100K records; and the table can be hit by 5000+ visits/day on avg. So I just rewrote it to get same results... as in my blog entry linked below.
No Jeff I can't fire the developer 😉 It is 3rd party vendor :). I wrote about this on my blog (link); I can't seen any advantage to this. Any comments?
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 26, 2009 at 2:57 pm
Not about the code itself, but do you have permission from the vendor to post their code up in public? If not, it might be a good idea to write your own example based on the general idea and use that in your blog post.
As for the code, it's a common enough pattern. I suspect it has to do with a procedural mindset - first do x, then do y, then do z, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 26, 2009 at 4:07 pm
Hi Gale,
This is an example from me, I created exact same style. I did not post their code; any recommendations I can do from SQL Side to improve it? I am getting TempDB relocated to a new SAN hdd next week to give tempdb it's own I/O... what else can I do?
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 26, 2009 at 11:40 pm
I think give it a try by using temp table instead of table variable , then crate index on temp table for the field whic is in where clause , so it wont go for table scan then, it will go for index seek.
i may be wrong to give this suggesstion but i tried to give ...
Mithun
April 26, 2009 at 11:49 pm
Heh thanks. I was thinking same thing; but I don't have access to code. That is just my example, thanks for suggestion :D.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 27, 2009 at 1:50 am
Mohit (4/26/2009)
any recommendations I can do from SQL Side to improve it? I am getting TempDB relocated to a new SAN hdd next week to give tempdb it's own I/O... what else can I do?
If you can't change the SQL code then, other than ensuring good indexes on the permanent tables, there's very little that can be done.
How many rows are in those table variables?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2009 at 2:50 am
Hey Mohit,
Sounds like you're doing all you can. My only other thoughts would be to:
1. Ensure that SQL Server has sufficient memory installed so that the data stays in buffer pool; and
2. Rewrite the code segment for performance and submit it to the company concerned.
I know the second option sounds unlikely to met with much interest, but I did this once and the third party made a modified version available to their customers as a patch, which was also included in the next maintenance pack.
There's really no harm in doing it, and if you approach them with a positive attitude, you may get a good result. You would also have the warm and fuzzy feeling of having benefited other customers.
Cheers,
Paul
edit: P.S. The first option is important (memory available to SQL Server) since table variables, like all temporary tables, are only written to disk under memory pressure. If there is very low memory pressure, the chances are good that data in the temporary variable will never touch physical storage - not even log records, since table variables are not logged. I wouldn't want to discourage you from putting tempdb on its own disk though, for all sorts of other good reasons.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 27, 2009 at 8:54 am
GilaMonster (4/27/2009)
If you can't change the SQL code then, other than ensuring good indexes on the permanent tables, there's very little that can be done.How many rows are in those table variables?
In my experiment I populated it with 1000 rows; using that sample code it was about 2300 reads, and using adjust code that is just selecting the rows it was 16 reads. So that's why I asked them to change it, but the actual tables have any where from 20,000 rows to 150,000 rows and continuously growing.
Paul White (4/27/2009)
Hey Mohit,Sounds like you're doing all you can. My only other thoughts would be to:
1. Ensure that SQL Server has sufficient memory installed so that the data stays in buffer pool; and
....
edit: P.S. The first option is important (memory available to SQL Server) since table variables, like all temporary tables, are only written to disk under memory pressure. If there is very low memory pressure, the chances are good that data in the temporary variable will never touch physical storage - not even log records, since table variables are not logged. I wouldn't want to discourage you from putting tempdb on its own disk though, for all sorts of other good reasons.
Thanks Paul. I am getting the memory expanded to 32GB for just that reason; because of selecting all rows continuously memory is under pressure. I am hoping for exact same thing that pages can stay in memory so I avoid physical reads; but the writes to tempdb are still going to be high. So I am moving that to a RAID1+0 also and setting starting size high enough so it doesn't have to expand.
Thanks everyone for Ideas :-D. I just wanted to see if I was missing something....
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 27, 2009 at 9:36 am
Mohit (4/27/2009)
In my experiment I populated it with 1000 rows; using that sample code it was about 2300 reads, and using adjust code that is just selecting the rows it was 16 reads. So that's why I asked them to change it, but the actual tables have any where from 20,000 rows to 150,000 rows and continuously growing.
And those table variables have all of those 20,000 rows to 150,000 rows in them?
If so, there's little else that you can do to help performance here other than ensuring that the indexes on the permanent tables are optimal and trying to get the code rewritten. That number of rows combined with a lack of stats on table vars means that the last query will have a plan optimal for 1 row. If there's a lot more than 1 row, it will not perform well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2009 at 10:00 am
Gale,
So question, when the plan gets creating using table variables? How long do the plans stay in cache? Even though the objects are destroyed right away? As in the actual code they use 5-6 other variables to control parameters and such.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 27, 2009 at 10:20 am
Mohit (4/27/2009)
So question, when the plan gets creating using table variables? How long do the plans stay in cache?
Exactly the same as for any other query.
Plan gets created when the query runs if there is no matching plan in cache. Plan gets cached until it's aged out or a change to the underlying schema makes it invalid.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2009 at 12:14 pm
I may be wrong, nothing available to do any testing, but isn't this the equivalent of the code you originally posted above?
SELECT
M.MainTableID,
M.PageName,
S.PageData1,
S.PageData2,
ptype.PageTypeID,
ptype.Description AS PageType
FROM
MainTable AS M
INNER JOIN SubTable AS S
ON M.MainTableID = S.MainTableID
INNER JOIN PageTypeTable AS ptype
ON M.PageTypeID = ptype.PageTypeID
WHERE
ptype.PageTypeID = 89
Makes me wonder about those who developed the package you are using.
April 27, 2009 at 1:58 pm
Heh Lynn,
Exactly I wondered to, it looks like the entire thing is dynamically created because I saw similar code with 4-5 variations when I was tracing. In each case exact same type of starting with later filtering/ordering/group etc.
I have yet to convince them this can cause blocking issues..... so all I can do is upgrade hardware to nth degree and hope it survives for few months longer.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply