SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to speed up query in SQL Compact 3.5?
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

WarrenW
Starting Member

USA
19 Posts

Posted - 12/10/2013 :  14:04:50  Show Profile  Reply with Quote
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

USA
37143 Posts

Posted - 12/10/2013 :  14:06:30  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Pakistan
885 Posts

Posted - 12/10/2013 :  14:35:41  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
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

USA
19 Posts

Posted - 12/10/2013 :  15:08:35  Show Profile  Reply with Quote
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

USA
37143 Posts

Posted - 12/10/2013 :  15:10:39  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
19 Posts

Posted - 12/10/2013 :  15:14:06  Show Profile  Reply with Quote
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

USA
37143 Posts

Posted - 12/10/2013 :  15:37:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
37143 Posts

Posted - 12/10/2013 :  15:38:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Pakistan
885 Posts

Posted - 12/10/2013 :  15:46:09  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
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

Sweden
30265 Posts

Posted - 12/10/2013 :  16:42:14  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

USA
19 Posts

Posted - 12/10/2013 :  22:08:42  Show Profile  Reply with Quote
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

USA
19 Posts

Posted - 12/11/2013 :  08:51:34  Show Profile  Reply with Quote
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

USA
37143 Posts

Posted - 12/11/2013 :  12:56:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
37143 Posts

Posted - 12/11/2013 :  12:57:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
19 Posts

Posted - 12/12/2013 :  08:32:47  Show Profile  Reply with Quote
What does DDL stand for? The database layout?
Go to Top of Page

WarrenW
Starting Member

USA
19 Posts

Posted - 12/12/2013 :  10:55:38  Show Profile  Reply with Quote
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

USA
37143 Posts

Posted - 12/12/2013 :  13:07:12  Show Profile  Visit tkizer's Homepage  Reply with Quote
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)

Singapore
17650 Posts

Posted - 12/12/2013 :  20:30:45  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

WarrenW
Starting Member

USA
19 Posts

Posted - 12/12/2013 :  20:49:06  Show Profile  Reply with Quote
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)

Singapore
17650 Posts

Posted - 12/12/2013 :  21:08:52  Show Profile  Reply with Quote
how big is that table ?

try on a much smaller table


KH
Time is always against us


Edited by - khtan on 12/12/2013 21:09:20
Go to Top of Page

WarrenW
Starting Member

USA
19 Posts

Posted - 12/13/2013 :  09:07:26  Show Profile  Reply with Quote
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
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000