OK, enough brain cells are dead.
SQL Fiddle
WITH cte AS
(
SELECT
[ICFilterID],
[ParentID],
[FilterDesc],
[Active],
CAST(0 AS varbinary(max)) AS Level
FROM [dbo].[ICFilters]
WHERE [ParentID] = 0
UNION ALL
SELECT
i.[ICFilterID],
i.[ParentID],
i.[FilterDesc],
i.[Active],
Level + CAST(i.[ICFilterID] AS varbinary(max)) AS Level
FROM [dbo].[ICFilters] i
INNER JOIN cte c
ON c.[ICFilterID] = i.[ParentID]
)
SELECT
[ICFilterID],
[ParentID],
[FilterDesc],
[Active]
FROM cte
ORDER BY [Level];
For starters gid
should probably be a numeric type. integer
should be good enough or bigint
if the key space shouldn't be big enough. Much smaller footprint, faster processing than with character data, faster and smaller indexes.
More importantly, to improve performance I suggest database normalization.
Quote:
There is a fairly regular pattern where each word appears about 1000 times.
Create a separate table for unique words:
CREATE TABLE word (
word_id serial
, word text
);
Fill it with unique instances of word
in your big_tbl
:
INSERT INTO word (word)
SELECT DISTINCT word
FROM big_tbl
ORDER BY word;
ORDER BY
is optional, not needed for query at hand. But it speeds up index creation and might be cheaper overall.
The table should be small in comparison: only ~ 50k rows for 50M rows in your big table.
Add indexes after filling the table:
ALTER TABLE word
ADD CONSTRAINT word_word_uni UNIQUE (word) -- essential
, ADD CONSTRAINT word_word_id_pkey PRIMARY KEY (word_id); -- expendable?
If those are read-only tables, you can do without the pk. It's not relevant to the operations at hand.
Replace your big table with a much smaller new table. You may have to lock the big table to avoid concurrent writes. Concurrent reads are not a problem.
CREATE TABLE big_tbl_new AS
SELECT b.gid -- or the suggested smaller, faster numeric replacement
, w.word_id, b.stat
FROM big_tbl b
JOIN word w USING (word)
ORDER BY word; -- sorting by word helps query at hand
ORDER BY
clusters the data (once) making the query at hand faster, because far fewer blocks have to be read (unless your data is clustered mostly already). The sort carries a cost, weigh cost and benefit once more.
DROP big_tbl; -- make sure your new table has all data!
ALTER big_tbl_new RENAME TO big_tbl;
Recreate indexes:
ALTER TABLE big_tbl ADD CONSTRAINT big_tbl_gid_pkey PRIMARY KEY (gid); -- expendable?
CREATE INDEX big_tbl_word_id_idx ON big_tbl (word_id); -- essential
Your query looks like this now and should be faster:
SELECT b.*
FROM word w
JOIN big_tbl b USING (word_id)
WHERE w.word = 'something';
Reorganization is meant to be a one-time operation to re-organize your data. Keep the new form and also consider keeping indexes permanently.
All of this together (including new indexes) should occupy about half of what you had before on disk, also cutting the time for creation in half (at least). Index creation should be considerably faster, the query as well. If RAM is a limiting factor, these modification pay double.
If you have to write to the table as well, it becomes more expensive (but you did not mention anything about that). You'd need to adjust your logic for DELETE
/ UPDATE
/ INSERT
:
Example for INSERT
: Fetch word_id
for existing words or insert a new row in word
returning the new word_id. Details for this:
How do I insert a row which contains a foreign key?
Best Answer
First thing you should do is Apply SQL Server 2008 R2 SP3 ASAP. This is because SQL Server 2008 R2 RTM version is
not supported at all
by Microsoft. There are lots of fixes which have been included in this Service pack release and that would surely benefit SQL Server query performance.NO IT IS NOT. One should only rebuild index which is fragmented above certain value. Probably widely used value is, if index fragmentation is >30 you should go for rebuild and it is in between 10 and 30 you should go for reorganize. Please note you have standard edition so index rebuild would not be online. If you rebuild index blindly it would cause more downtime and produce more logs and thus more overhead on system. If you have narrow maintenance window you need to be highly selective with index rebuild and all this can be taken care if you go for Ola Hallengren Index rebuild solution
Other thing to note is if page_count for index, page_count can be seen from be seen from sys.dm_db_index_physical_stats , is less than 1000 you don't need to rebuild such indexes. The reason is since page count is less pages allocated to index would be from mixed extent and these mixed pages can be lying anywhere and hence producing Logical fragmentation even after rebuild. But rest assured such small page count indexes would not affect query performance at all. You can read more about why such indexes still remain fragmented even after rebuil here
There are various options you can choose with index rebuild you can see more details here. Lot depends on your environment and configuration
You can read more about query plans from This Article. You should focus more on actual query plan than estimated query plan. Actual would tell you what exactly SQL Server is doing and what resources it is using.
You can also try SQL Sentry Plan explorer tool to get better insight into SQL Server execution plan