Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shiraabr
Starting Member
3 Posts |
 Posted - 2008-11-18 : 09:14:21
|
| Hi All,I have a paging system - it presents the total number of record, and the first 10 records.The two queries basically do the same (complicated) logic and same (long) code to fetch the desired records, and then the first one performs a count, and the second one preforms paging (using the ROW_BUMBER() technique).So - I have 2 queries with the same complicated code. This is not good for all the classic duplicate code reasons.I tried to generate a new help procedure that only performs the complicated fetching, and then call it from the other two funcs. This caused a performance hit in the paging func. If I don't use the help func - I guess when the optimizer sees that the fetched records are going to be paged, he doesn't bring all of them. But if I call the help procedure - it looks like all the records are fetched, and only then being paged.So, any ideas how I can remove this code duplication without the performance hit? I can't use a view (and I'm not sure it would have helped..) because the complicated code takes dynamic parameters.Any help would be welcome :) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-18 : 09:17:14
|
| without seeing the code, we cant suggest much. can you post the duplication code part? |
 |
|
|
shiraabr
Starting Member
3 Posts |
Posted - 2008-11-18 : 09:49:19
|
| Sorry, I cant publish the code :/Its basically a WITH statement with 5 tables, and then the 6th table is an intersection of all the 5 tables.The 6th table is then used in the first proc with a Count(*), and in the second proc with a paging technique. |
|
|
|
shiraabr
Starting Member
3 Posts |
Posted - 2008-11-18 : 10:06:52
|
| OK - I solved it myself, using inline table-valued function.Thanks for helping :) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-18 : 10:58:07
|
quote:
Originally posted by shiraabr
OK - I solved it myself, using inline table-valued function.Thanks for helping :)
Cool  In future, if you cant post code, just give a sample code which illustrates your scenario. we dont require your actual code but at the same time need to understand your scenario. so sample code will do. |
|
|
|
|
|
|
|
|
Duplicate code