MSDE Performance

Hello to all,
I am using MSDE and i recently realised that the query performance is
decreasing while db size increases. In a db table of 20000 rows a simple
select query with 6 inner joins took 1.27 sec. to execute (XP Pro, 775 MHz,
248 MB). I upgraded to SQL Server personal edition and the same query was
executed instantly. I tested it in many different pc and the results were
the same.
Could it be that MSDE performance is so dramatically lower than SQL Server?
And if this is the case then for which applications is MSDE suitable for?
Any advice is much appreciated.
Thanks,
Theo
Hi
MSDE 2000 does not support query parallelism and some other tuning features.
If you woul have more RAM, it could cache more data, negating the effect of
the simpler query processor.
Regards
Mike
"Theo" wrote:

> Hello to all,
> I am using MSDE and i recently realised that the query performance is
> decreasing while db size increases. In a db table of 20000 rows a simple
> select query with 6 inner joins took 1.27 sec. to execute (XP Pro, 775 MHz,
> 248 MB). I upgraded to SQL Server personal edition and the same query was
> executed instantly. I tested it in many different pc and the results were
> the same.
>
> Could it be that MSDE performance is so dramatically lower than SQL Server?
> And if this is the case then for which applications is MSDE suitable for?
>
> Any advice is much appreciated.
> Thanks,
>
> Theo
>
>
|||Hi,
i am not sure i understand what you mean when you refer to query parallelism
but this is the query i am having problems with:
SELECT
cls_Suppliers.Supplier, lot_Grouping.BatchCode, cls_Products.Product,
cls_Color.Description AS ClDesc,
cls_Class.Description AS CsDesc, cls_Size.Description AS SzDesc,
cls_Boxes.Box, lot_Production.LabelCode,
lot_Production.lot_ID, cls_Color.Code AS ClCode, cls_Class.Code AS CsCode,
cls_Size.Code AS SzCode, cls_Suppliers.Code AS SpCode, cls_Boxes.Code AS
BCode, cls_Products.Code AS PCode, cls_Boxes.BoxWeight
FROM
lnk_lot_tracing lnk_lot_tracing_1
LEFT OUTER JOIN lot_Grouping ON lnk_lot_tracing_1.lot_in =
lot_Grouping.lot_ID
RIGHT OUTER JOIN lot_Production ON lnk_lot_tracing_1.lot_out =
lot_Production.lot_ID
LEFT OUTER JOIN cls_Suppliers ON lot_Production.YREF_Suppliers =
cls_Suppliers.OID
LEFT OUTER JOIN cls_Color ON lot_Production.YREF_Color = cls_Color.OID
LEFT OUTER JOIN cls_Products ON lot_Production.YREF_Products =
cls_Products.OID
LEFT OUTER JOIN cls_Class ON lot_Production.YREF_Class = cls_Class.OID
LEFT OUTER JOIN cls_Boxes ON lot_Production.YREF_Boxes = cls_Boxes.OID
LEFT OUTER JOIN cls_Size ON lot_Production.YREF_Size = cls_Size.OID
RIGHT OUTER JOIN lnk_lot_tracing ON lot_Production.lot_ID =
lnk_lot_tracing.lot_in
WHERE
lnk_lot_tracing.lot_out = 'PAL05109000005'
ORDER BY
cls_Suppliers.Supplier, lot_Grouping.BatchCode, CsDesc, ClDesc, SzDesc,
cls_Boxes.Box
In a XP Home, 3.0 GHz pc, 204 MB pc they query was executed in 30 sec. Again
after upgrading to SQL Server the execution was instant. I read in an
article about MSDE features that optimum performance is limited to five
concurrent batch workloads. Does that mean five concurrent transactions,
five concurrent connections, or something totally different that i don't get
?
What do you mean by 'simpler query processor'? Is it that apart from the
concurrent workload limitation, MSDE processes queries differently that SQL
Server?
Regards,
Theo
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net>
news:69D4E639-EE00-4145-B07D-D4006E59FE09@.microsoft.com...
> Hi
> MSDE 2000 does not support query parallelism and some other tuning
features.
> If you woul have more RAM, it could cache more data, negating the effect
of
> the simpler query processor.
> Regards
> Mike
>
|||Query parallelism is where SQL Server splits up a query into multiple pieces,
assigns it to one or more processors. This enables multiple parts of the
query to run, without having to wait for each other. Then the results are
merged at the end.
The workload governor will kick in at 8 concurrent processes. 3 are used by
the system , so if you submit 5 queries, over 5 connections at the same time,
and a 6th one comes in, all the queries get slowed down.
I have found MSDE to be less aggressive when requesting memory. Standard
Edition will ask sooner when it realizes it needs more. MSDE is assumed to
run on someone's desktop PC, so it needs to behave better so that other
applications can also do their work and keep the system responsive.
The governor and performance limiting is a bit of a 'black box' that
Microsoft has not chosen to disclose the full workings of. Figuring out how
it behaves is not very easy so it is hard to compare.
SQL Server Express (SQL Server 2005's version of MSDE), does not have a
query governor it in and supports larger databases, but will only use 1
processor and at maximum 1GB RAM. The 'black box' uncertainty falls away.
Regards
Mike
"Theo" wrote:

> Hi,
> i am not sure i understand what you mean when you refer to query parallelism
> but this is the query i am having problems with:
> ----
> SELECT
> cls_Suppliers.Supplier, lot_Grouping.BatchCode, cls_Products.Product,
> cls_Color.Description AS ClDesc,
> cls_Class.Description AS CsDesc, cls_Size.Description AS SzDesc,
> cls_Boxes.Box, lot_Production.LabelCode,
> lot_Production.lot_ID, cls_Color.Code AS ClCode, cls_Class.Code AS CsCode,
> cls_Size.Code AS SzCode, cls_Suppliers.Code AS SpCode, cls_Boxes.Code AS
> BCode, cls_Products.Code AS PCode, cls_Boxes.BoxWeight
> FROM
> lnk_lot_tracing lnk_lot_tracing_1
> LEFT OUTER JOIN lot_Grouping ON lnk_lot_tracing_1.lot_in =
> lot_Grouping.lot_ID
> RIGHT OUTER JOIN lot_Production ON lnk_lot_tracing_1.lot_out =
> lot_Production.lot_ID
> LEFT OUTER JOIN cls_Suppliers ON lot_Production.YREF_Suppliers =
> cls_Suppliers.OID
> LEFT OUTER JOIN cls_Color ON lot_Production.YREF_Color = cls_Color.OID
> LEFT OUTER JOIN cls_Products ON lot_Production.YREF_Products =
> cls_Products.OID
> LEFT OUTER JOIN cls_Class ON lot_Production.YREF_Class = cls_Class.OID
> LEFT OUTER JOIN cls_Boxes ON lot_Production.YREF_Boxes = cls_Boxes.OID
> LEFT OUTER JOIN cls_Size ON lot_Production.YREF_Size = cls_Size.OID
> RIGHT OUTER JOIN lnk_lot_tracing ON lot_Production.lot_ID =
> lnk_lot_tracing.lot_in
> WHERE
> lnk_lot_tracing.lot_out = 'PAL05109000005'
> ORDER BY
> cls_Suppliers.Supplier, lot_Grouping.BatchCode, CsDesc, ClDesc, SzDesc,
> cls_Boxes.Box
> ----
>
> In a XP Home, 3.0 GHz pc, 204 MB pc they query was executed in 30 sec. Again
> after upgrading to SQL Server the execution was instant. I read in an
> article about MSDE features that optimum performance is limited to five
> concurrent batch workloads. Does that mean five concurrent transactions,
> five concurrent connections, or something totally different that i don't get
> ?
>
> What do you mean by 'simpler query processor'? Is it that apart from the
> concurrent workload limitation, MSDE processes queries differently that SQL
> Server?
>
> Regards,
> Theo
>
>
> ? "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> Y??á?? ó?? ìTX?ìá
> news:69D4E639-EE00-4145-B07D-D4006E59FE09@.microsoft.com...
> features.
> of
>
>
|||Thanks a lot Mike, this has been helpful indeed.
Theo.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net>
news:7B72D454-5D64-46BF-9E02-C643C049DA73@.microsoft.com...
> Query parallelism is where SQL Server splits up a query into multiple
pieces,
> assigns it to one or more processors. This enables multiple parts of the
> query to run, without having to wait for each other. Then the results are
> merged at the end.
> The workload governor will kick in at 8 concurrent processes. 3 are used
by
> the system , so if you submit 5 queries, over 5 connections at the same
time,
> and a 6th one comes in, all the queries get slowed down.
> I have found MSDE to be less aggressive when requesting memory. Standard
> Edition will ask sooner when it realizes it needs more. MSDE is assumed to
> run on someone's desktop PC, so it needs to behave better so that other
> applications can also do their work and keep the system responsive.
> The governor and performance limiting is a bit of a 'black box' that
> Microsoft has not chosen to disclose the full workings of. Figuring out
how
> it behaves is not very easy so it is hard to compare.
> SQL Server Express (SQL Server 2005's version of MSDE), does not have a
> query governor it in and supports larger databases, but will only use 1
> processor and at maximum 1GB RAM. The 'black box' uncertainty falls away.
> Regards
> Mike