Best practice when to use an index

Question

Best practice when to use an index

Viewing 15 posts - 16 through 30 (of 53 total)

You must be logged in to reply to this topic. Login to reply

stax68 SSChampion Points: 11711 More actions · Answer 1

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

David.Poole SSC Guru Points: 76001 More actions · Answer 2

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.

LinkedIn Profile

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

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]

David.Poole SSC Guru Points: 76001 More actions · Answer 4

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.

LinkedIn Profile

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 5

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

David.Poole SSC Guru Points: 76001 More actions · Answer 6

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.

LinkedIn Profile

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

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]

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 8

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

Edwin1608 SSC Journeyman Points: 99 More actions · Answer 9

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...

stax68 SSChampion Points: 11711 More actions · Answer 10

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

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]

stax68 SSChampion Points: 11711 More actions · Answer 12

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

David.Poole SSC Guru Points: 76001 More actions · Answer 13

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.

LinkedIn Profile

Frank Kalis SSC Guru Points: 111183 More actions · Answer 14

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]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 15

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]