Author |
Topic |
WarrenW
Starting Member
20 Posts |
Posted - 2013-12-10 : 14:04:50
|
Hello,I have a .NET program that runs a query on a table in SQL Compact 3.5. The device is a handheld scanner with Windows CE 5.0. The problem is the age of the scanner and the table has 650K records. How can I create an index on a field? Does this version support an index?Thanks,Warren |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-10 : 14:06:30
|
I've never heard of any editions/versions not supporting indexes on regular tables. create index IndexName on TableName(ColumnName)Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2013-12-10 : 14:35:41
|
For compact edition an index can be created before there is data in the table.--------------------------http://connectsql.blogspot.com/ |
|
|
WarrenW
Starting Member
20 Posts |
Posted - 2013-12-10 : 15:08:35
|
Thanks. Should I see a drastic change in the amount of time for a query? Right now it can take two minutes for a query! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-10 : 15:10:39
|
quote: Originally posted by WarrenW Thanks. Should I see a drastic change in the amount of time for a query? Right now it can take two minutes for a query!
We can't answer that with the little information you've provided. We'd need to see the query for starters. The DDL of the tables involved would be helpful too.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
WarrenW
Starting Member
20 Posts |
Posted - 2013-12-10 : 15:14:06
|
Okay thanks. It's a basic query such as SELECT UserID, UserName, UserAddress FROM Table WHERE UserID = 1234 But there are 650K records in there. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-10 : 15:37:16
|
quote: Originally posted by WarrenW Okay thanks. It's a basic query such as SELECT UserID, UserName, UserAddress FROM Table WHERE UserID = 1234 But there are 650K records in there.
Do you have an index on UserID? Is it the PK? If so, is it clustered?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-10 : 15:38:08
|
quote: Originally posted by lionofdezert Tara is correct. There are possibilities that your Query is not written as optimizer friendly and just creating index can never help you to improve its performance.
You misstated what I wrote. Adding indexes does often improve performance. My point was that we couldn't answer the question without seeing the query and hopefully also DDL.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2013-12-10 : 15:46:09
|
quote: Originally posted by tkizer
quote: Originally posted by lionofdezert Tara is correct. There are possibilities that your Query is not written as optimizer friendly and just creating index can never help you to improve its performance.
You misstated what I wrote. Adding indexes does often improve performance. My point was that we couldn't answer the question without seeing the query and hopefully also DDL.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
I apologize for that--------------------------http://connectsql.blogspot.com/ |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-12-10 : 16:42:14
|
Primary key on UserID if possible.Or nonclustered index on UserID and INCLUDE other columns to make the index covering.CREATE NONCLUSTERED INDEX IX_Table ON dbo.Table (UserID) INCLUDE (UserName, UserAddress); Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
WarrenW
Starting Member
20 Posts |
Posted - 2013-12-10 : 22:08:42
|
Well, I created an index on the DB and loaded all of the records. I copied it onto the device and I get the error "Not enough storage is available to complete this operation." This is at the very beginning before the first form even loads. I open the connection at the beginning and leave it open. I had another error before so I added the Max Database Size = 300; to the connection string. I had it higher but set it to 300 because the SDF file size is 267MB. This is installed on a storage card but do not know how much memory is available on it. What am I running out of? Space on the memory card? Any thoughts? There is nothing else on the memory card. When I look at the card properties it shows no space available but that says it even when the files are deleted.Thanks!!!!Warren |
|
|
WarrenW
Starting Member
20 Posts |
Posted - 2013-12-11 : 08:51:34
|
Also, I created a new index like CREATE UNIQUE INDEX... should I have done it with NONCLUSTERED instead? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-11 : 12:56:26
|
It sounds like you don't have enough disk space to support the index. Indexes take up space.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-11 : 12:57:40
|
quote: Originally posted by WarrenW Also, I created a new index like CREATE UNIQUE INDEX... should I have done it with NONCLUSTERED instead?
We can't answer that without you providing us more information, but if you used CREATE UNIQUE INDEX syntax then you got a non-clustered index anyway as it's the default unless it's the PK. You likely want this index to be clustered, but without seeing the DDL for the table it's hard to help.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
WarrenW
Starting Member
20 Posts |
Posted - 2013-12-12 : 08:32:47
|
What does DDL stand for? The database layout? |
|
|
WarrenW
Starting Member
20 Posts |
Posted - 2013-12-12 : 10:55:38
|
I have the DB on an SD card with 2GB free. The DB file size is under 300MB. It works fine until I create an index. I appreciate everyone's help. Any suggestions? I will get the layout to post. I know one of the fields I indexed on might be a PK. It doesn't need to be though. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-12-12 : 13:07:12
|
DDL stands for data definition language. We need to see the CREATE TABLE command as well as the commands for the constraints (might be included in the CREATE TABLE) and for the indexes. We only need to see this information for the tables involved in the query you are trying to resolve.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-12 : 20:30:45
|
quote: Originally posted by WarrenW Well, I created an index on the DB and loaded all of the records. I copied it onto the device and I get the error "Not enough storage is available to complete this operation." This is at the very beginning before the first form even loads. I open the connection at the beginning and leave it open. I had another error before so I added the Max Database Size = 300; to the connection string. I had it higher but set it to 300 because the SDF file size is 267MB. This is installed on a storage card but do not know how much memory is available on it. What am I running out of? Space on the memory card? Any thoughts? There is nothing else on the memory card. When I look at the card properties it shows no space available but that says it even when the files are deleted.Thanks!!!!Warren
quote: Originally posted by tkizer It sounds like you don't have enough disk space to support the index. Indexes take up space.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Have you try increase the Max Database Size ? KH[spoiler]Time is always against us[/spoiler] |
|
|
WarrenW
Starting Member
20 Posts |
Posted - 2013-12-12 : 20:49:06
|
Hello,I just tried setting it to 1000 and I still get the message "Not enough storage is available to complete this operation. I created a new index on the database indexing only the one field ISBN and the size of the file went down to 109mb. But same error. As long as I have an index this fails. It fails right on opening the one connection at the beginning of the program. Windows CE 5.0 and Compact 3.5 using VB.NET in VS2008. 650,000 records in the table.http://www.tybeesoftware.com/bookinv.pngI am dead in the water until I can get this resolved. Any other suggestions?Here is a screen shot of the table. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-12-12 : 21:08:52
|
how big is that table ?try on a much smaller table KH[spoiler]Time is always against us[/spoiler] |
|
|
WarrenW
Starting Member
20 Posts |
Posted - 2013-12-13 : 09:07:26
|
It has 650,000 records in it and the file is 109MB. Its only when I add an index I have the problem. |
|
|
Next Page
|