Re: Very slow

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


.

Relevant Pages

  • Re: What are OleDbParameter names with Original_ prefix?
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... Since the table has a primary key column, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ADO.NET Transaction
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... Client transactions put too much schema-dependent, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Performance problems with OracleDataReader
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... referencing the columns by ordinal is far faster than using strings. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Very slow
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ...
    (microsoft.public.sqlserver.ce)
  • Re: ADO.NET Transaction
    ... The SqlBulkCopy class can take anything you can expose with a DataReader and send it to SQL Server in a single high-speed operation. ... Hitchhiker's Guide to Visual Studio and SQL Server ... and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook) ... Client transactions put too much schema-dependent, ...
    (microsoft.public.dotnet.framework.adonet)
Loading