|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-02-24 : 19:36:08
|
| Janet writes "sql7, svcpk4, nt4, svcpk6a +I have a dynamic sql stored procedure that I'm basically concatenating and then executing, but it needs to be FASTER. I started with views and temp tables, did more research, then changed my joins to derived tables and that helped. I tried special indexes for tblCategories and tblAgeGroups, but it was slower. I made a table of data combining keywords from my xtblStats table and table Titles from my xtblTitle table (it's probably only going to be updated quarterly, so if I HAVE to, I can do this, but I'd really prefer not to.) The kicker is that the keywords field is in the tblStats table and the tblTitle is in the xtblTitle table.But, I'm still too slow. tblStats has the detailed line data (keywords) connecting up through pk SIDtblStatTitles has the detailed line data titles connecting up through pk tableID, xtblTitle has the table titles connecting up through pk ssID, xtblSS has the spreadsheet connecting up through pk sourceID, xtblSource has the originating data source title with pk sourceIDtblCategories is a many-to-many with catID, SID and 512,757 rowstblAgeGroups is a many-to-many with ageID, SID and 888,100 rowstblStatTitles has 507,454url is: http://www.mtn.ncahec.org/cmty/regionalStats/index2.asp but you can only list by tables right now and that's as far as it goes (If you post this, please take out the url)Any tips you can pass along would be lovely. (I read something about calling stored procedures and using returns within stored procedures, but can't find any documentation on it.) Thanks a bunch.REATE PROCEDURE pTblList @src as int, @cat as int, @age as int, @sex as int, @race as int, @hisp as int, @year as int, @kwds as varChar(100) AS declare @sql as varChar(2000) SET NOCOUNT ON begin--sixth and final to get sourceShortset @sql='select ns5.tblID, tblTitle, ns5.sourceID, pr6.sourceShort from chad.dbo.xtblSource pr6, ' --fifth nest to get sourceID set @sql=@sql + ' (select pr5.sourceID, ns4.tblID, ns4.tblTitle from chad.dbo.xtblSS pr5, ' --fourth nest to get table titles set @sql=@sql + ' (select pr4.tblID, pr4.tblTitle, ssID from chad.dbo.xtblTitles pr4, ' --third nested select to get table tblID set @sql=@sql + ' (select pr3.tblID from chad.dbo.tblStatTitles pr3, ' --second nested select for parameters other than age or categories set @sql=@sql + ' (select pr2.titleID from chad.dbo.tblStats pr2, ' begin --first nested select to get categories if @age=0 if @age=0 set @sql=@sql + ' (select SID from chad.dbo.tblCategories where catID=' + CONVERT(varChar(2),@cat) +' ) as ns1 ' else --or first nested select to get categories if @age<>0 begin set @sql=@sql + ' (select pr.SID from chad.dbo.tblCategories pr, ' set @sql=@sql + ' (select SID from chad.dbo.tblAgeGroups where ageID=' + CONVERT(varChar(2),@age) +') as ns ' set @sql=@sql + ' where catID=' + CONVERT(varChar(2),@cat) set @sql=@sql + ' AND ns.SID=pr.SID) as ns1 ' end end set @sql=@sql + ' where ns1.SID=pr2.SID ' --end of second nest --additional selections if @sex<>0 set @sql=@sql + ' AND (pr2.sexID=' + CONVERT(varChar(2),@sex) + ') ' if @race<>0 set @sql=@sql + ' AND (pr2.raceID=' + CONVERT(varChar(2),@race) + ') ' if @hisp<>0 set @sql=@sql + ' AND (pr2.hispID=' + CONVERT(varChar(2),@hisp) + ') ' if @year<>0 set @sql=@sql + ' AND (pr2.[year]=' + CONVERT(varChar(4),@year) + ') ' set @sql=@sql + ' group by titleID) as ns2 ' set @sql=@sql + ' where pr3.titleID=ns2.titleID ' --end of third nest maybe if len(@kwds)>1 -- keywords search begin< |
|
Stored Procedure - more speed 