Re: Very slow
- From: "William \(Bill\) Vaughn" <billvaRemoveThis@xxxxxxxxx>
- Date: Fri, 22 Jun 2007 09:45:02 -0700
Frankly, in my experience, SQLCe has been faster for many
operations--especially simple ones. I would look again at the indexes being
used. SSMS can show the query plan it will use--see if it's using the
indexes correctly.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
<d.wiss@xxxxxxx> wrote in message
news:1182490400.961017.73550@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I know that but that couldn't be the explanation why the CE has >1
minute while MSDE do this in < 1 second.
Anyway thank you for your try to help.
Daniel
On 21 Jun., 19:00, "William \(Bill\) Vaughn"
<billvaRemoveT...@xxxxxxxxx> wrote:
Consider that the two SQL engines share no binaries. SQLCe then SQL
Mobile
(now named SQL Server Compact) is an ISAM engine.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speakerwww.betav.com/blog/billvawww.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visitwww.hitchhikerguides.netto get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
<d.w...@xxxxxxx> wrote in message
news:1182416194.230185.179710@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi Bill
Yes all necessary primary keys and indexes are done. In the original
TableA has more columns but to test I reduced it to two columns each
and it is still slow.
I have only the execution plan from MSDE and this looks neat. And it
doesn't matter if I make "...TableA LEFT OUTER JOIN Table B..." or
"...TableB RIGHT OUTER JOIN TableA...". MSDE optimize them to the
exactely same execution plan.
The fastest way I found is to use a subquery like this:
SELECT Col1 FROM TableA WHERE Col1 NOT IN (SELECT DISTINCT Col1 FROM
TableB)
This takes a few seconds but will increase while TableB will growing.
We could live with them. I'm just wondering why the Compact Edition is
that slow.
Daniel
On 20 Jun., 17:55, "William \(Bill\) Vaughn"
<billvaRemoveT...@xxxxxxxxx> wrote:
Are the appropriate indexes set up? Did you look at the query plan
generated
by each?
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speakerwww.betav.com/blog/billvawww.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visitwww.hitchhikerguides.nettoget more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
<d.w...@xxxxxxx> wrote in message
news:1182349867.716505.303800@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi all
Assume there are two tables with each 2 columns. TableA has 3000
rows
and TableB has 30'000 rows and each row of TableA.Col1 is linked as
a
foreign key to TableB.Col1. Primary keys, indexes etc are all well
done.
Now I want to find out if there are some items in TableA which ARE
NOT
found in TableB (zombie records). I know this shouldn't happen but
there are...
So I do following query:
SELECT DISTINCT TableA.Col1, TableB.Col1 FROM TableA
LEFT OUTER JOIN TableB ON TableB.Col1 = TableA.Col1 WHERE
TableB.Col1
IS NULL
In case there is a zombie record it will look like:
TableA.Col1 TableB.Col1
====================
1234 NULL
If I let run this query on MSDE it is very fast.
If I let run this query in VS 2005 on a Compact Edition database it
is
veeeeery slow. It takes aprox. 1 minute and the CPU is 100%.
Has someone an explanation? Or better: has someone a better
solution?
Thanks
Daniel
.
- References:
- Very slow
- From: d . wiss
- Re: Very slow
- From: William \(Bill\) Vaughn
- Re: Very slow
- From: d . wiss
- Re: Very slow
- From: William \(Bill\) Vaughn
- Re: Very slow
- From: d . wiss
- Very slow
- Prev by Date: Re: Very slow
- Next by Date: Sync/Merge local sql compact databases to single global database
- Previous by thread: Re: Very slow
- Next by thread: Sync/Merge local sql compact databases to single global database
- Index(es):
Relevant Pages
|
