|
hi, I have a table with 28898683 rows and without a primary key. the select query takes 25 mins to retrieve all the data. I tried creating a Pr.key but got error because of duplicate rows. Tried creating an index with (ignore_dup_key = ON) but same error. please help, so that the data retrieval is fast. Tauseef
(comments are locked)
|
|
You don't necessarily need a primary key (although one wonders how you uniquely identify the data in your table without having one) but instead an index. Probably you need a clustered index. Use the CREATE INDEX syntax to make one using the column (or columns) that is most commonly used to retrieve your data. http://msdn.microsoft.com/en-us/library/ms188783.aspx
(comments are locked)
|
|
Having an index on this table wont speed up the time to get the data when you select all the data. Whether it is an index or a table, all the data is all the data and that needs to be read from the disk and passed to your client. If your query however selects only a few columns from the table and then filters out a lot of rows with a WHERE clause then indexing will help. When you tried to create the primary key did you expect the columns to be unique in their values? Do you think you have a data integrity issue? As Grant says, you can still create a clustered index on your table that will significantly improve query times - so long as you are filtering out rows and not requesting every column. The CI will help as long as they aren't just firehosing data.
May 23 '12 at 12:43 PM
Blackhawk-17
"firehosing" - I like that term! The phrase " ... retrieve all the data." makes me jumpy.
May 23 '12 at 01:36 PM
Fatherjack ♦♦
(comments are locked)
|


How are you trying to create the index? What DDL statement are you using?
Could you provide some examples of the queries you are running against the table?