| Author |
Topic |
|
Hariarul
Posting Yak Master
160 Posts |
 Posted - 2007-08-28 : 11:04:09
|
| Hi all,We have a table that has around 30 columns with 50 million records. There is a stored procedure that is used for a random search for records from this table. The sp takes all the 30 column values as parameters and tries to fetch result set.Most of the parameter default value are NULL and only a single column is mandatory ( this column is not the primary key ). Several OR clause like " (@param is NULL or col1 = @param) " statements are used in WHERE clause and in AND Clause.There are few indexes existing on the table. The response time seems to be very high when we execute the sp. Any ideas based on your experience to speed up the execution time of the sp or any other smart way to handle the scenario.Any help would be appreciated.Thanks ,Hariarul |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-28 : 11:05:12
|
Have you tried FREETEXT search?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-08-28 : 11:08:19
|
| No Peso, The columns here are of mixed datatype CHAR(10), Datetime , VARCHAR(20) etc. Will FREETEXT search help in this case too ? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-28 : 11:10:39
|
Yes.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-08-28 : 11:15:41
|
| Let me check on that now. Thanks. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-28 : 11:25:34
|
| how big would the catalog be for 50 million rows?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-08-28 : 11:28:20
|
| We are using 2005 , how do I find the catalog size ? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-08-28 : 11:55:00
|
you have to build it first. let us know  _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 07:21:18
|
I would use sp_ExecuteSQL and do it dynamically. It would help if some of the scenarios are recurring (because their query plans will get cached). it would probably also help if the Dynamic SQL could be generated at the application end, rather than SQL Server [that statement should raise the heat in here a bit!]DECLARE @strSQL nvarchar(4000)SELECT @strSQL = 'SELECT Col1, Col2, ... FROM dbo.MyTable1 JOIN dbo.MyTable2 ON ID1 = ID2 WHERE 1=1 'IF @param1 IS NOT NULL SELECT @strSQL = @strSQL + 'AND col1 = @param1 'IF @param2 IS NOT NULL SELECT @strSQL = @strSQL + 'AND col2 = @param2 'SELECT @strSQL = @strSQL + 'ORDER BY ColX'EXEC sp_executesql @strSQL, N'@param1 varchar(10), @param2 int', @param1, @param2 Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-29 : 07:25:12
|
| Actually, I do sometimes use another route. if a small number of indexed columns are frequently involved I have done:Is @Param1 NOT NULL? Yes -> Get matching PKs into a Temp Table filtering on the other INDEXED @ParamN values at the same time.ELSEIFRepeat for other @ParamN which are indexedELSEDo what you do now.If your Params caused you to go the first route then FILTER based on all your @Params:SELECT ...FROM @MyTempVarTableJOIN MyActualTable ...which cuts down the number of rows being processed.For the "first step" stuff you may want to exec separate Sprocs for each one, so that each can have a single query which is best-optimised and cached.Kristen |
|
|
|
Hariarul
Posting Yak Master
160 Posts |
Posted - 2007-08-29 : 08:10:28
|
| Thanks Kristen. I already used your second option (PKs to temptable) to bring down the response time. Thanks Everyone. |
|
|
|
|
All columns in WHERE clause.