Why is this ORDER BY with related field very slow in v12?

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

Why is this ORDER BY with related field very slow in v12?

Jim Hays
There must be something we are doing wrong.

I am running an ORDER BY call with the same database running in 4D 2004 and
v12.
Repeatedly running the same ORDER BY:

  40 seconds in v12 - first time and every time after
    5 seconds in 2004 the first time
    2 seconds in 2004 every subsequent time

In v12, it always creates a temporary file during the order by process,
which then goes away.

Windows Server 2003 R2
4GB RAM on the machine
4D v12.5.122263

Data file is 265MB
Index file is 60MB
Database Cache setting in v12 - tried 1GB; 500GB, and Calculation of
adaptive cache checked.
Have compacted the data in v12

ORDER BY([child_table];[parent_table]date_field)
Using pointers actually: ORDER BY($Table_p->;$Field_p->;<)

There are 160,000 records in the child table.
There are 6,700 records in the parent table.

There is an automatic relation for Many to One.
All fields are indexed.
Relating ID fields are Alpha 15.

Have also tried running on a different server and single user.  Basically
the same behavior.

Any ideas?

Thanks,

Jim Hays

**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Why is this ORDER BY with related field very slow in v12?

Scott Gelvin
Jim,

You said your ORDER BY was performed across table where the relationship is based on an Alpha15 field.

Remember, v2004 stored its data in ASCII, but v12 stores its data in Unicode. Thus, each index key value is larger. A greater amount of memory is required to perform the sort algorithm -- not just in the data cache, but in the main application memory.

Furthermore, it is very likely it must be a larger contiguous memory block. The fact that you are seeing a Temporary file created in this process suggests there is more data being sorted on that 4D wants to keep in a contiguous block in memory.

I see three options:
* try 64-bit 4D server
* re-evaluate your schema -- de-normalize so the sorting isn't cross-table
* reduce the number of records you're sorting

Scott Gelvin
Footprints, Inc.
4D specialists since 1987



**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Why is this ORDER BY with related field very slow in v12?

Jim Hays
In reply to this post by Jim Hays
FWIW, using some alternate code from Wayne Stewart, the sort can be done in
9 seconds.
It uses selection to array, sort array, and create selection from array.

Look for "wbs_OrderBy" on the NUG.

But, it is not the answer for this case.  There is no reason we shouldn't
get this sort speed back down to what 4D 2004 can do.

- Jim



On Fri, Nov 15, 2013 at 10:32 AM, Jim Hays <[hidden email]> wrote:

> There must be something we are doing wrong.
>
> I am running an ORDER BY call with the same database running in 4D 2004
> and v12.
> Repeatedly running the same ORDER BY:
>
>   40 seconds in v12 - first time and every time after
>     5 seconds in 2004 the first time
>     2 seconds in 2004 every subsequent time
>
> In v12, it always creates a temporary file during the order by process,
> which then goes away.
>
> Windows Server 2003 R2
> 4GB RAM on the machine
> 4D v12.5.122263
>
> Data file is 265MB
> Index file is 60MB
> Database Cache setting in v12 - tried 1GB; 500GB, and Calculation of
> adaptive cache checked.
> Have compacted the data in v12
>
> ORDER BY([child_table];[parent_table]date_field)
> Using pointers actually: ORDER BY($Table_p->;$Field_p->;<)
>
> There are 160,000 records in the child table.
> There are 6,700 records in the parent table.
>
> There is an automatic relation for Many to One.
> All fields are indexed.
> Relating ID fields are Alpha 15.
>
> Have also tried running on a different server and single user.  Basically
> the same behavior.
>
> Any ideas?
>
> Thanks,
>
> Jim Hays
>
>

**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Why is this ORDER BY with related field very slow in v12?

Jim Hays
In reply to this post by Scott Gelvin
Yes, we are in Unicode mode.

But isn't there plenty of cache space to handle this?  We've got 1GB of
cache.

For the Alpha 15 index (roughly)
16 bytes * 160,000 records = 2.5MB
64 bytes * 160,000 records = 10MB (being generous for UTF-16)

(and some more for the parent index field, and the date field we're
ordering by, so make it 25MB)

And 2004 is 20 times faster than v12??





On Fri, Nov 15, 2013 at 10:51 AM, Scott Gelvin <[hidden email]> wrote:

> Jim,
>
> You said your ORDER BY was performed across table where the relationship
> is based on an Alpha15 field.
>
> Remember, v2004 stored its data in ASCII, but v12 stores its data in
> Unicode. Thus, each index key value is larger. A greater amount of memory
> is required to perform the sort algorithm -- not just in the data cache,
> but in the main application memory.
>
> Furthermore, it is very likely it must be a larger contiguous memory
> block. The fact that you are seeing a Temporary file created in this
> process suggests there is more data being sorted on that 4D wants to keep
> in a contiguous block in memory.
>
> I see three options:
> * try 64-bit 4D server
> * re-evaluate your schema -- de-normalize so the sorting isn't cross-table
> * reduce the number of records you're sorting
>
> Scott Gelvin
> Footprints, Inc.
> 4D specialists since 1987
>
>
>
> **********************************************************************
> 4D v13 is available now - with more than 200 new features to make
> your applications richer and faster
> http://www.4d.com/products/new.html
>
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************
>

**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Why is this ORDER BY with related field very slow in v12?

Jeffrey Kain
If you see a temp file, then the answer the is no, you don’t have enough cache. Or at least a large enough block of free memory in the cache.

Are you using the 64-bit server?

On Nov 15, 2013, at 11:10 AM, Jim Hays <[hidden email]> wrote:

> But isn't there plenty of cache space to handle this?  We've got 1GB of
> cache.


**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Why is this ORDER BY with related field very slow in v12?

Scott Gelvin
In reply to this post by Jim Hays
Jim,

> But isn't there plenty of cache space to handle this?  We've got 1GB of
> cache.
>
> For the Alpha 15 index (roughly)
> 16 bytes * 160,000 records = 2.5MB
> 64 bytes * 160,000 records = 10MB (being generous for UTF-16)

It may not actually be Data Cache you need to be worried about, but process memory. Either way, as Jeff indicated, the temporary file means it wants a larger block of memory than it can grab _in a contiguous chunk_.

Scott Gelvin
Footprints, Inc.
4D specialists since 1987
**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Why is this ORDER BY with related field very slow in v12?

Jim Hays
In reply to this post by Jeffrey Kain
Well, I just tried 64bit server and still have the problem.
Running on my Windows 7 machine with 12 GB RAM

[x] Calculation of adaptive cache
Memory reserved for others:   128 MB
Pct available used for cache   80%
Min size  128 MB
Max size  8000 MB

While the ORDER BY is in progress, the server monitor says 455MB memory
used, and 7 GB free.

Also tried
- without the box checked and 3000 MB of cache.
- keep cache in memory at 8000 MB
neither made a difference


Surely it isn't a cache problem.  Maybe a continuous block of RAM?
Tried Process stack size up to 4MB - is that really involved though?


Well, in our case, I can just disallow sorting on selections over N rows,
so I think we just reduce that number and punt on this one.





On Fri, Nov 15, 2013 at 11:17 AM, Jeffrey Kain <[hidden email]>wrote:

> If you see a temp file, then the answer the is no, you don’t have enough
> cache. Or at least a large enough block of free memory in the cache.
>
> Are you using the 64-bit server?
>
> On Nov 15, 2013, at 11:10 AM, Jim Hays <[hidden email]> wrote:
>
> > But isn't there plenty of cache space to handle this?  We've got 1GB of
> > cache.
>
>
> **********************************************************************
> 4D v13 is available now - with more than 200 new features to make
> your applications richer and faster
> http://www.4d.com/products/new.html
>
> 4D Internet Users Group (4D iNUG)
> FAQ:  http://lists.4d.com/faqnug.html
> Archive:  http://lists.4d.com/archives.html
> Options: https://lists.4d.com/mailman/options/4d_tech
> Unsub:  mailto:[hidden email]
> **********************************************************************
>

**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Why is this ORDER BY with related field very slow in v12?

Spencer Hinsdale-2

Another difference between v2004 and v12 would be how it follows relations (least resistance vs. first found).

If you're not already, you might try something like:
automatic relations(false;false) `being careful to restore as needed
set field relation([table]key; manual; automatic)

-spencer


On Nov 15, 2013, at 9:09 AM, Jim Hays <[hidden email]> wrote:

> Well, I just tried 64bit server and still have the problem.


**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Why is this ORDER BY with related field very slow in v12?

Keith White
In reply to this post by Jim Hays
Hi Jim

Hope all is well with you.

I'd say submit this as a tech case, if that's an option for you.  Can you test with v13?  If so, it's best to submit with v13 as if there is something that could be optimised for this use case, it won't get done in v12.

Best regards

Keith White
Synergist Express Ltd, UK.



**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Why is this ORDER BY with related field very slow in v12?

Jim Hays
> automatic relations(false;false) `being careful to restore as needed
> set field relation([table]key; manual; automatic)

Interesting - tried it, but did not help.

Hi Keith!
Yes, I think I will open a tech case.  Maybe after I make the usual example
database to test first!
Oh, and I did try with v13 as well.  (On a MacBookPro with SSD).
Surprisingly slow with the SSD.  It still took close to 30 seconds.

Jim

**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Why is this ORDER BY with related field very slow in v12?

Mike Vogt-2
In reply to this post by Jim Hays
Jim,
I believe v12 reacts more adversely to circular relationships.  Is there more than one way your relations traverse from one table to the other through other tables to the table your sorting from?

Mike Vogt

Message: 9
Subject: Why is this ORDER BY with related field very slow in v12?
Date: Fri, 15 Nov 2013 10:32:57 -0500
From: Jim Hays <[hidden email]>
------------------------------------------------------------

**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Why is this ORDER BY with related field very slow in v12?

Jim Hays
In reply to this post by Jim Hays
>I believe v12 reacts more adversely to circular relationships.  Is there
more than one way your relations traverse from >one table to the other
through other tables to the table your sorting from?

Just to be sure, I deleted the other relation from the child table.  Still
the same result.

It would have had to go:
child -> other parent -> grandma <- parent to sort by

**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************
Reply | Threaded
Open this post in threaded view
|

Re: Why is this ORDER BY with related field very slow in v12?

Keisuke Miyako
Hello,

if you are seeing temporary files being created,
it is likely that your selection exceeds the order-by ceiling.
the cache implements a limit as to how much physical memory a process can consume,
to protect other processes. you can lift this restriction with SET DATABASE PARAMETER.

miyako

2013/11/16 4:01、"Jim Hays" <[hidden email]> のメッセージ:

>> I believe v12 reacts more adversely to circular relationships.  Is there
> more than one way your relations traverse from >one table to the other
> through other tables to the table your sorting from?
>
> Just to be sure, I deleted the other relation from the child table.  Still
> the same result.
>
> It would have had to go:
> child -> other parent -> grandma <- parent to sort by






**********************************************************************
4D v13 is available now - with more than 200 new features to make
your applications richer and faster
http://www.4d.com/products/new.html

4D Internet Users Group (4D iNUG)
FAQ:  http://lists.4d.com/faqnug.html
Archive:  http://lists.4d.com/archives.html
Options: https://lists.4d.com/mailman/options/4d_tech
Unsub:  mailto:[hidden email]
**********************************************************************