Re: Very slow


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.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: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: Access Migration to SQL
    ... yeah because Tom's a little crybaby pussy who doesn't know how to use SQL Server ... First thing to check, make sure that all the primary keys are correctly defined for each tables and that if you have multiple indexes for a table, that the primary key is clusterized and if it's not, that there are no other index clusterized and that the name for the primary key is the first one in alphabetical order. ... If it's still too slow after that, you will have to dig further: use Terminal Server is you are on a very slow LAN or on a VPN, change the design of your frontend in order to retrieve the minimal number of records.; create and use Views, use passthrough queries, create your own recordsets or use unbound forms, switch to ADP or even better, switch to .NET. ... For ADP, take a look at all the previous posts in this newsgroup; for unbound forms, take a look at the books from Mary Chipman and Baron or from Klander, for creating your own recordsets, see http://support.microsoft.com/?kbid=281998. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Access Migration to SQL
    ... you need to move it to SQL Server, and then rewrite all your queries, sprocs and views on the database side and not on the clientside ... First thing to check, make sure that all the primary keys are correctly defined for each tables and that if you have multiple indexes for a table, that the primary key is clusterized and if it's not, that there are no other index clusterized and that the name for the primary key is the first one in alphabetical order. ... If it's still too slow after that, you will have to dig further: use Terminal Server is you are on a very slow LAN or on a VPN, change the design of your frontend in order to retrieve the minimal number of records.; create and use Views, use passthrough queries, create your own recordsets or use unbound forms, switch to ADP or even better, switch to .NET. ...
    (microsoft.public.access.adp.sqlserver)
  • Processing a cube with less than clean data
    ... None of the tables had primary keys, ... I have no control over the process that populates the data in ... was to set it up to use SQL Server authentication. ... BI project to ServerA (SQL Server 2005) and populating it with data ...
    (microsoft.public.sqlserver.olap)
  • Re: Im completely stuck.............
    ... don't use Identity columns as primary keys. ... Your app can generate the primary key value without any help from SQL Server ... When the user wants to add a child row, use the Guid as the foreign key. ... SQL doesn't autogenerate the Guids, ...
    (microsoft.public.dotnet.languages.vb)
  • Identity crisis: GUID VS. Range
    ... I'm developing a project in which I have several Sql Server Express and ... central Sql Server 2005 database. ... Assign ranges of primary keys in each subscriber in order to assign ...
    (microsoft.public.sqlserver.replication)
Loading