Re: Very slow performance by Cache 5.0.2.607.1

Nuno Canas Wed, 12 May 2004 10:11:33 +0100

Kashif, You're comparing apples with lemons. You've all the tables setted up in SQLSRV with indexes and relationships that you have not in Cache. So, if you know what kind of data and speed you need, you have to create the apropriate indexes in Cache. The import utility just imports the data it doesn't build the indexes for you.

As suggested by Kevin, and it happens in all Databases I know, the count(*) is a full table scan! So I suggest to use count(1) or count(ID) to reach the same result. This applies to Cache, SQLSRV, Oracle, etc.

Any "group by" clause should have an index.
Try to create an index on it: to your class:

Index itemclassIndex on itemclass;

We hope to ear from you.

Nuno

Kashif wrote:

I am running the query after importing in Cache

----- Original Message ----- From: "Nuno Canas" <[EMAIL PROTECTED]> Newsgroups: intersystems.public.cache Sent: Wednesday, May 12, 2004 02:55 Subject: Re: Very slow performance by Cache 5.0.2.607.1

Kashif,
You mean import or linked the tables to Cache ?
Nuno
kashif wrote:
SunflowerHi im evaluating Cache 5.0.2.607.1. and copied a single table

from

my SQL Server to Cache.
The table has around 25 columns with just 36000 rows.

I executed

select itemclass, count(*) as Count
from items
group by itemclass

It takes around 1.3 seconds on Cache, while on MSDE the result shows up
immediately.
and if i add  say "having count(*) < 100" it takes 3 seconds.

Now this is some thing i was not expecting from Cache. (after reading
Marketing stuff )
After this i didnt even tried to import the bigger tables to Cache.

I hope some of you might provide some helpfull tuning advices or share
simmilar problems.

Thanks in advance


--------------050001020307030105010404
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
 <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
 <title></title>
</head>
<body text="#000000" bgcolor="#ffffff">
Kashif,<br>
You're comparing apples with lemons.<br>
You've all the tables setted up in SQLSRV with indexes and
relationships that you have not in Cache.<br>
So, if you know what kind of data and speed you need, you have to
create the apropriate indexes in Cache.<br>
The import utility just imports the data it doesn't build the indexes
for you.<br>
<br>
As suggested by Kevin, and it happens in all Databases I know, the
count(*) is a full table scan!<br>
So I suggest to use count(1) or count(ID) to reach the same result.
This applies to Cache, SQLSRV, Oracle, etc.<br>
<br>
Any "group by" clause should have an index.<br>
Try to create an index on it: to your class:<br>
<br>
<basefont name="Courier New"> Index itemclassIndex on itemclass;<br>
<font color="#000000"></font><br>
We hope to ear from you.<br>
<br>
Nuno<br>
<br>
Kashif wrote:<br>
<blockquote type="cite"
cite="[EMAIL PROTECTED]">
 <pre wrap="">I am running the query after importing in Cache

----- Original Message ----- From: "Nuno Canas" <a class="moz-txt-link-rfc2396E" href="mailto:[EMAIL PROTECTED]"><[EMAIL PROTECTED]></a> Newsgroups: intersystems.public.cache Sent: Wednesday, May 12, 2004 02:55 Subject: Re: Very slow performance by Cache 5.0.2.607.1


 </pre>
 <blockquote type="cite">
   <pre wrap="">Kashif,

You mean import or linked the tables to Cache ?
Nuno

kashif wrote:

   </pre>
   <blockquote type="cite">
     <pre wrap="">SunflowerHi im evaluating Cache 5.0.2.607.1. and copied a single 
table
     </pre>
   </blockquote>
 </blockquote>
 <pre wrap=""><!---->from
 </pre>
 <blockquote type="cite">
   <blockquote type="cite">
     <pre wrap="">my SQL Server to Cache.
The table has around 25 columns with just 36000 rows.

I executed

select itemclass, count(*) as Count
from items
group by itemclass

It takes around 1.3 seconds on Cache, while on MSDE the result shows up
immediately.
and if i add  say "having count(*) &lt; 100" it takes 3 seconds.

Now this is some thing i was not expecting from Cache. (after reading
Marketing stuff )
After this i didnt even tried to import the bigger tables to Cache.

I hope some of you might provide some helpfull tuning advices or share
simmilar problems.

Thanks in advance


     </pre>
   </blockquote>
 </blockquote>
</blockquote>
</body>
</html>

--------------050001020307030105010404--

Re: Very slow performance by Cache 5.0.2.607.1

Reply via email to