Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to speed up query in SQL Compact 3.5?

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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/
Go to Top of Page

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!
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



I apologize for that

--------------------------
http://connectsql.blogspot.com/
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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?
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

WarrenW
Starting Member

20 Posts

Posted - 2013-12-12 : 08:32:47
What does DDL stand for? The database layout?
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Have you try increase the Max Database Size ?




KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

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

I am dead in the water until I can get this resolved. Any other suggestions?

Here is a screen shot of the table.
Go to Top of Page

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]

Go to Top of Page

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.
Go to Top of Page
    Next Page

- Advertisement -