SQL Mobile query hangs PPC 2003 device

Hot Topic

Copying database using "Copy Database"
How to view Cube in WEB Browser(IE) like Pivot tables of Excel
AS2005 September CTP Memory Error
SQL 2005 DB design
sql server help
This SMO transfer script doesnt transfer ?
For security reasons DTD is prohibited in this XML document
OUTER JOIN table limit?
Recovery Model of Subscription Database
Error deploying SSIS package to SQL Server

SQL Server

NEWSEQUENTIALID behavior changed from Apr CTP to June CTP
How to disable Directory Browsing
Variables in CONTAINSTABLE. IS IT POSSIBLE? NO ERRORS AND NO RESULTS.
I'm retarded and need help with Derived Column or Script Task
SQL Server Express Security?
AVG Function Overflow
Get LangId By nchar(1)
Cutting & Pasting Tasks
how do i kill off an old cursor in memory
Complex SQL Query

SQL Mobile query hangs PPC 2003 device

I've written a query to pull data from a SQL Mobile database which seems to completely hang the device when I run it (I waited several minutes before warm-starting). Yes, it's a bit complex, but it works fine on the desktop version--completes pretty much instantly.

Are there any limits to SQL Mobile that might prevent me from writing such a query There are not very many records in any of the tables (all are <20, except one which is ~2500).

Here is the query:

------------------------------------code---------------------------

SELECT oi.ProductID, g.GenusName, Species.SpeciesName, f.FormName, f.Abbr, sz.Sizes, su.SizeUnits, su.Abbr, pa.PackageName, pa.Abbr, oi.NumRequested, oi.NumProvided

FROM OrderItems oi

JOIN Products p ON p.ProductID=oi.ProductID

JOIN Varieties v ON p.VarietyID = v.VarietyID

JOIN Genus g ON v.GenusID=g.GenusID

JOIN Species ON v.SpeciesID=Species.SpeciesID

JOIN Grades gr ON gr.GradeID=p.GradeID

JOIN Form f ON gr.FormID=f.FormID

JOIN SizeUnits su ON gr.SizeUnitsID=su.SizeUnitsID

JOIN Sizes sz ON gr.SizeID = sz.SizeID

JOIN Packages pa ON p.PackageID=pa.PackageID

WHERE oi.OrderID=3

------------------------------------------code---------------------------------------

Any ideas Thanks in advance.

-Kevin

Answer this question

SQL Mobile query hangs PPC 2003 device

  • Winterchill

    Hi Syed. Thanks for responding.

    I did not try it on another device. I'm populating my .sdf database using rda.pull's of each of the tables in question. When I run the query using SQL 2005 Management Studio on the original (master) database, it completes right away. I will give it a whirl on the emulator to see what happens.

    I haven't tried moving the files to a storage card--I have 20 Mb of free memory on my device (far more than on my card) and the database is only ~400kB in size. Surely that is enough...

    -Kevin

  • SmithaNarreddi

    One other thing--if I eliminate the reference to the OrderItems table (which has about 40 entries) the query completes in about 1 second. So, I found that I can work around the problem by a) pulling the prodID from each OrderItem, and then running the rest of the query once for each prodID. This is slow and cumbersome, but it does work.

    Doesn't explain the problem though...

    -Kevin

  • J. Aldrin

    Thank you Syed--it looks like I have some learning to do. I will pursue this.

    -Kevin

  • jesse_j3000

    SQL Mobile includes support for showplans, which help assess and optimize queries.

    Copy the .sdf file to desktop and open a connection from SQL 2005 Management Studio, run the query and generate a showplan. You may add indexes to the tables and explore the showplan.

    Take a look at the "Query Performance Tuning" topic in SQL Mobile Books Online.

    Thank you!

    Syed N. Yousuf

    Microsoft Developer Support Professional

    This posting is provided “AS IS” with no warranties, and confers no rights.



  • Gerhard Zehetbauer

    Just to verify - does it work OK on another device or when connecting to this .sdf database from SQL 2005 Management Studio

    Have you tried moving the .sdf and Temporary Database (Temp File) to a storage card How much free space is available on the device

    Thank you!

    Syed N. Yousuf

    Microsoft Developer Support Professional

    This posting is provided “AS IS” with no warranties, and confers no rights.



  • SQL Mobile query hangs PPC 2003 device
Answer this question