Tag Info

New answers tagged

3

Could I through indexing alone influence SQL server to run this much faster. Possibly. There are all sorts of things you could try with indexing, including creating a filtered index to exclude the 95% of UserPasswordRequestHash entries that are null, expanding existing indexes to include more columns, or adjusting indexes so the chances of finding the ...


1

Without knowing details about exactly what type of queries are accessing that data and the indexes you are dealing with a precise answer is not possible. However, in general an index rebuild drops and then re-adds that index and locking needs to occur for that to happen. If you have queries that are attempting to access this table while the rebuild is going ...


1

Since you are optimizing for reads you could simply add one optimal index per query. So if your query is select price_per_unit from Sales where exclude = 0 and partner = 42 Create the index (exclude, partner) include (price_per_unit). You say that writes and disk spaces are not of significant concern. So add the optimal indexes to support your reads.


1

You can try selecting into a temp table with the zip criteria only, and then select from the temp table with the additional criteria. SELECT * INTO #MyTemp FROM (SELECT * FROM MyTable where zip='1234') data SELECT * from #MyTemp where other = 'Y' DROP Table #MyTemp Not sure if this will be faster, but since the zip-only query runs fast, then you're just ...


1

Is there a better way to write this type of query? No, the way you've written it is the "best" way. Unless, of course, that way doesn't work. The joy and frustration of using a declarative language is the optimiser. It is your best friend when it works and worst enemy when it doesn't. One way to kick the optimiser into doing the right thing is to ...


2

I highly recommend this alternative approach to get an IMMUTABLE unaccent() function: CREATE OR REPLACE FUNCTION f_unaccent(text) RETURNS text AS $func$ SELECT unaccent('unaccent', $1) $func$ LANGUAGE sql IMMUTABLE SET search_path = public, pg_temp; Use that function for the expression index (and in all queries). Detailed explanation in this related ...


0

Low Selectivity Here is a good quote from SqlServerCentral: In general, a nonclustered index should be selective. That is, the values in the column should be fairly unique and queries that filter on it should return small portions of the table. The reason for this is that key/RID lookups are expensive operations and if a nonclustered index is ...


4

This should largely be a Google question and might get marked, but I'll add an answer just in case it helps others. Please see a blog post by the Brent Ozar crew: http://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/ First off: ‘Reorganize’ and ‘Rebuild’ are two different operations that each reduce fragmentation in an ...



Top 50 recent answers are included