Best practice when to use an index
-
SARG = Search argument - the column that is being searched. I think this is more common amongst Oracle programmers for some reason(?).
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
quote:
.. If its an internet app then not very much x huge audience = major headache.Not sure if I get the meaning
Cheers,
Frank
A small performance hit that would normally be too small to be worth measuring becomes a huge performance hit when the audience becomes large enough.
-
Hi David,
thanks for explanation!
quote:
I would tend to index the table on principle but if it is virtually static I would make sure that the index has a very high fill factor.
so you're having a PrimaryKey on such tables?
quote:
I would say that indexing a ten row table would not normally be expected to do much but it depends on the demands being placed on your server. If its an internet app then not very much x huge audience = major headache.
when you speak of huge audience what number do you have in mind?
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I always put a primary key on tables even if I have to use an identity column to force one. Thinking back to my MS Access days the SQL Server recordset recturned by MS Access used to be read-only when there was no primary key.
I tend to save my clustered index for something that would benefit from it. Normally this would be the primary key but I don't feel there is much to be gained from clustering an identity column.
As far as audience figures is concerned its a bit of a "how long is a piece of string" (twice the distance from the end to the middle)! We have one site with 10,000 concurrent users and indexing a table with 15 values made a huge difference. I stress that I don't have any metrics for audience size vs performance gap.
I tend to use the English weather forecasting method (throw a stone in the air and if it comes down its going to rain) for predicting potential problems.
-
Just thinking off the wall for a moment, would a stored procedure with a case statment containing the values be quicker?
If the procedure is called often, it would be cached so execution time would be quick. There are no I/O issues so there is no need to think about an index. I think that SQL Server starts reading at the start of a data page until it reaches the record it wants which is the same as a case statement.
The big downside (and probably a very good reason not to use this approach) is that you would have to recreate the procedure every time you changed the values.
Any ideas on whether this would be quicker? I'm curious.
Jeremy
-
quote:
Just thinking off the wall for a moment, would a stored procedure with a case statment containing the values be quicker?
How about storing the values in a table but having a trigger that re-writes the stored procedure. That way you have the maintainability plus the advantage of the compiled and cached stored procedure.
-
Hi David,
quote:
I always put a primary key on tables even if I have to use an identity column to force one. Thinking back to my MS Access days the SQL Server recordset recturned by MS Access used to be read-only when there was no primary key.
that is also where I came from. But what used to be a good habit isn't necessarily the best. At least it is worth thinking over when you have time to.
quote:
I tend to use the English weather forecasting method (throw a stone in the air and if it comes down its going to rain) for predicting potential problems.
reminds me of some evil joke with mathematicians and algebraic sign errors.
I tend to avoid any kind of hardcoding. When I see our mainframe guys handling another hardcoded thing, I'm regularly cautiously said amused
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
I agree about avoiding hard coding but David's idea of recreating a stored procedure using a trigger 'gets around' hard coding the proc - not that I am suggesting you do it.
Would it be faster than pure table based solution?
Jeremy
-
Another thought: Create the table from an SP into the TEMPDB at SQL start-up if the values do not alter often, but are needed often. The result is a faster look-up - worth considering...
-
Or use
DBCC PINTABLE <t>
SELECT * into #t FROM <t>
DROP TABLE #t
at startup to load and keep the table in the buffer cache.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Hi Jeremy,
quote:
I agree about avoiding hard coding but David's idea of recreating a stored procedure using a trigger 'gets around' hard coding the proc - not that I am suggesting you do it.
to be honest, at the moment I'm trying to figure out what David meant and how this can be done
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Frank,
the idea is to have a trigger on the table which gets the new values from the table, and generates a stored proc which will take an input param identifying the parameter you require, and which will output the parameter's value using a case statement.
So the trigger might cursor through all the data in the table to generate the case statement, then add the rest of the code. Alternatively, it might just do a REPLACE for the changed value(s) on the sp text from syscomments. In either case it would EXEC the resulting 'alter proc' string.
CREATE and ALTER PROC aren't allowed in triggers in SQL 7-, though I haven't checked whether using 'EXEC()' could get round this -but I doubt it.
Tim
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
quote:
reminds me of some evil joke with mathematicians and algebraic sign errors.
Not "Solution for constipated mathematicians" work it out with pencil and paper and if that doesn't work use logs?
My use of trigger to build a stored proc would involve building the ALTER PROC statement as a string then executing the string.
I wouldn't necessarily use a trigger to do the actual building, possibly just to enable a job to be run.
-
Aah,
now I see the light again.
Thanks, Tim!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Hi David,
quote:
Not "Solution for constipated mathematicians" work it out with pencil and paper and if that doesn't work use logs?
no, I thought more of something like this
http://www.workjoke.com/projoke22.htm
There is also a good page about programmers
http://www.workjoke.com/projoke20.htm
Enjoy!
Cheers,
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 16 through 30 (of 53 total)
You must be logged in to reply to this topic. Login to reply