Re: query performance SE vs IDS

On Tue, 30 Dec 2003 06:19:42 -0500, Jack wrote: > Hi, > > i'm looking for arguments to explain why several dbaccess on a single table > are 2 times slower with ids than se. i understand this test isn't quite > pertinant.The table is organize differently. The sqexplain shows an estimate > cost equal to 1. i don't think so it could be less? Thanks for noticing this > thread. To add to the Clown's comments: Similar version levels for IDS & SE (SE 5 had a more primitive optimizer than SE7 or IDS 7/9)? What level of stats are there on the table(s) involved? Are the index keys the same? Do the sqexplain files show similar query paths for both IDS & SE (same index chosen, etc.)? How much cache is there on the IDS instance? What level of PDQPRIORITY was set during the test? If high level of PDQ set, are there other complex queries running? Is the table fragmented on IDS? Are you testing IDS & SE on the same machine or different machines? If different: Same CPU, #CPUs & Speed or different? Either way: Same disk structures, number, size and speed of drives or different? Either way: Same OS or different? Has there been any attempt to tune the IDS instance for performance? Does this query match the types of queries for which it was tuned? Your question, while straight forward on its face, is obviously too complex to answer without further information. Art S. Kagel > Jack

"Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2003.12.30.15.46.11.80429.12806@bloomberg.net>... > On Tue, 30 Dec 2003 06:19:42 -0500, Jack wrote: > > > Hi, > > > > i'm looking for arguments to explain why several dbaccess on a single table > > are 2 times slower with ids than se. i understand this test isn't quite > > pertinant.The table is organize differently. The sqexplain shows an estimate > > cost equal to 1. i don't think so it could be less? Thanks for noticing this > > thread. > > To add to the Clown's comments: > > Similar version levels for IDS & SE (SE 5 had a more primitive optimizer than > SE7 or IDS 7/9)? > What level of stats are there on the table(s) involved? > Are the index keys the same? > Do the sqexplain files show similar query paths for both IDS & SE (same index > chosen, etc.)? > How much cache is there on the IDS instance? > What level of PDQPRIORITY was set during the test? > If high level of PDQ set, are there other complex queries running? > Is the table fragmented on IDS? > Are you testing IDS & SE on the same machine or different machines? > If different: Same CPU, #CPUs & Speed or different? > Either way: Same disk structures, number, size and speed of drives or different? > Either way: Same OS or different? > Has there been any attempt to tune the IDS instance for performance? > Does this query match the types of queries for which it was tuned? > > Your question, while straight forward on its face, is obviously too complex to > answer without further information. > > Art S. Kagel > > > Jack SE 7.25uc4 and IDS 9.30uc1 on some linux 2.4. i put the rootdbs chunk and se database in the same filesystem. i used stores7 database and the query file is : set isolation to dirty read; -- ids select fname from customer where customer_num=101; select fname from customer where customer_num=102;etc... i know quite stupid. and executed with dbaccess stores7 query.sql. ids gives very good response time when more than 1 user is working and with more complex queries.

Jack wrote: > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:<pan.2003.12.30.15.46.11.80429.12806@bloomberg.net>... >> On Tue, 30 Dec 2003 06:19:42 -0500, Jack wrote: >> >> > Hi, >> > >> > i'm looking for arguments to explain why several dbaccess on a single >> > table are 2 times slower with ids than se. i understand this test isn't >> > quite pertinant.The table is organize differently. The sqexplain shows >> > an estimate cost equal to 1. i don't think so it could be less? Thanks >> > for noticing this thread. >> >> To add to the Clown's comments: >> >> Similar version levels for IDS & SE (SE 5 had a more primitive optimizer >> than SE7 or IDS 7/9)? >> What level of stats are there on the table(s) involved? >> Are the index keys the same? >> Do the sqexplain files show similar query paths for both IDS & SE (same >> index chosen, etc.)? >> How much cache is there on the IDS instance? >> What level of PDQPRIORITY was set during the test? >> If high level of PDQ set, are there other complex queries running? >> Is the table fragmented on IDS? >> Are you testing IDS & SE on the same machine or different machines? >> If different: Same CPU, #CPUs & Speed or different? >> Either way: Same disk structures, number, size and speed of drives or >> different? Either way: Same OS or different? >> Has there been any attempt to tune the IDS instance for performance? >> Does this query match the types of queries for which it was tuned? >> >> Your question, while straight forward on its face, is obviously too >> complex to answer without further information. > > SE 7.25uc4 and IDS 9.30uc1 on some linux 2.4. > i put the rootdbs chunk and se database in the same filesystem. > i used stores7 database and the query file is : > set isolation to dirty read; -- ids > select fname from customer where customer_num=101; > select fname from customer where customer_num=102;> etc... i know quite stupid. > and executed with dbaccess stores7 query.sql. > ids gives very good response time when more than 1 user is working > and with more complex queries. What you're seeing is pretty much par for the course. Another place where IDS will run better is if the tables are really big. But you have to remember the code path for SE is much shorter than IDS, you'd probably find on this sort of query with a small database that SE would be faster than OnLine would be faster than IDS 7.x would be faster that 9.2x or 9.3x. One key point -- is your filesystem journalled? If it is, try making a raw partition for IDS and see if that isn't just a tiny bit faster... :o) -- "C'est pas parce qu'on n'a rien ''' dire qu'il faut fermer sa gueule" - Coluche