Re: Slow LOAD & SQL

>1. I would try increasing the BUFFERS (I would start with at least 5000) >2. Have you created a temporary dbspace? Although this shouldn't > be a problem, but sometimes it helps to increase the performance. >3. Have you tried to play around with OPTCOMPIND parameter ? >... > >Best Regards, >Octav > >On Sat, Mar 06, 1999 at 01:46:00PM -0200, Sebastian Paul Avarvarei wrote: >> Hi! >> >> I am making some comparisons between Informix and mssql (orc. soon). >> The test are based on the classic stores7 demo database, but adding >> 100k rows in "orders" and 650k rows in "items". I am looking especially >> for the DSS performance. >> >> Using LOAD (from SQL) or ONPLOAD it takes around 1:20 minutes for orders >> and 21:55 for items. With mssql I get 2:45 and 3:21. First strange thing. >> >> For most of the queries the results are normal (IDS 5-12 times faster, >> with far less memory usage). With one exception: >> >> select customer.company, sum(stock.unit_price*items.quantity) astot_order >> from orders, items, stock, customer >> where customer.customer_num=orders.customer_num and orders.order_num=items.order_num >> and items.stock_num=stock.stock_num and items.manu_code=stock.manu_code >> group by customer.company; >> >> I get 4:50 minutes with Informix and only 00:45 minutes with mssql. >> Can't understand why. Can someone give some ideas? The optimimzer tells me this: >> -------------------------------------------------------------- >> Estimated Cost: 197730 >> Estimated # of Rows Returned: 1 ##In fact there are 27, the number of companies## >> Temporary Files Required For: Group By >> >> 1) proteus.customer: SEQUENTIAL SCAN >> >> 2) proteus.orders: SEQUENTIAL SCAN >> >> DYNAMIC HASH JOIN (Build Outer) >> Dynamic Hash Filters: proteus.customer.customer_num = proteus.orders.customer_num >> >> 3) proteus.items: INDEX PATH >> >> (1) Index Keys: order_num >> Lower Index Filter: proteus.items.order_num = proteus.orders.order_num >> NESTED LOOP JOIN >> >> 4) proteus.stock: SEQUENTIAL SCAN >> >> DYNAMIC HASH JOIN >> Dynamic Hash Filters: (proteus.items.manu_code = proteus.stock.manu_code AND proteus.items.stock_num = proteus.stock.stock_num ) >> -------------------------------------------------------------- >> >> System informations: >> PII/350, 96MB RAM, one 6.4 GB IDE drive, NT 4.0/SP3 >> IDS 7.30 TC3 >> UPDATE STATISTICS HIGH for all columns in the WHERE clause.>> No logging for the database. Queries issued using dbaccess. >> SHMVIRTSIZE = 25MB, DS_TOTAL_MEMORY = 20MB.>> (Considering the fact that I do only reads, I kept BUFFERS to >> 200. Is it correct?) >> >> Thanks in advance >> Sebastian Paul A. >> E-mail: proteus@romus.com > >-- >Octav Chiriac Phone: (373) 2 21 20 96 >NetInfo S.R.L. Fax: (373) 2 21 36 59 >Chisinau (373) 2 24 00 83 >Moldova, Republic of mailto:com@netinfo-moldova.com > ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com

Obnoxio The Clown wrote: > > >1. I would try increasing the BUFFERS (I would start with at least > 5000) > >2. Have you created a temporary dbspace? Although this shouldn't > > be a problem, but sometimes it helps to increase the performance. > >3. Have you tried to play around with OPTCOMPIND parameter ? > >... > > > >Best Regards, > >Octav > > > >On Sat, Mar 06, 1999 at 01:46:00PM -0200, Sebastian Paul Avarvarei > wrote: > >> Hi! > >> > >> I am making some comparisons between Informix and mssql (orc. soon). > >> The test are based on the classic stores7 demo database, but adding > >> 100k rows in "orders" and 650k rows in "items". I am looking > especially > >> for the DSS performance. > >> > >> Using LOAD (from SQL) or ONPLOAD it takes around 1:20 minutes for > orders > >> and 21:55 for items. With mssql I get 2:45 and 3:21. First strange > thing. It just sounds like mssql's indexes are more simplistic and cheaper to update, perhaps. > >> For most of the queries the results are normal (IDS 5-12 times > faster, > >> with far less memory usage). With one exception: > >> > >> select customer.company, sum(stock.unit_price*items.quantity) as> tot_order > >> from orders, items, stock, customer > >> where customer.customer_num=orders.customer_num and > orders.order_num=items.order_num > >> and items.stock_num=stock.stock_num and > items.manu_code=stock.manu_code > >> group by customer.company; This is a four table join with no filters. The optimizer, by default will calculate the cost of EVERY possible combination of the four tables using each of the four tables first then second, etc. With four or more tables this can be expensive. Try setting the optimization level to low (SET OPTIMIZATION LOW) which will cause the optimizer to assume that the best choice for first table will still be best no matter which table is chosen second, etc. This reduces the number of possible query paths that need to be calculated from 256 to 24. The time needed to calculate the costs of 256 query paths will sometimes exceed the time to make the query and with four or more tables will almost always exceed any savings between the chosen path (with OPTIMIZATION LOW) and the optimum path (chosen with OPTIMIZATION HIGH). > >> I get 4:50 minutes with Informix and only 00:45 minutes with mssql. > >> Can't understand why. Can someone give some ideas? The optimimzer > tells me this: > >> -------------------------------------------------------------- > >> Estimated Cost: 197730 > >> Estimated # of Rows Returned: 1 ##In fact there are 27, the number of > companies## > >> Temporary Files Required For: Group By > >> > >> 1) proteus.customer: SEQUENTIAL SCAN > >> > >> 2) proteus.orders: SEQUENTIAL SCAN > >> > >> DYNAMIC HASH JOIN (Build Outer) > >> Dynamic Hash Filters: proteus.customer.customer_num = > proteus.orders.customer_num > >> > >> 3) proteus.items: INDEX PATH > >> > >> (1) Index Keys: order_num > >> Lower Index Filter: proteus.items.order_num = > proteus.orders.order_num > >> NESTED LOOP JOIN > >> > >> 4) proteus.stock: SEQUENTIAL SCAN > >> > >> DYNAMIC HASH JOIN HASH JOINs are not always the best choice. Change OPTCOMPIND to 0. > >> Dynamic Hash Filters: (proteus.items.manu_code = > proteus.stock.manu_code AND proteus.items.stock_num = > proteus.stock.stock_num ) > >> -------------------------------------------------------------- > >> > >> System informations: > >> PII/350, 96MB RAM, one 6.4 GB IDE drive, NT 4.0/SP3 > >> IDS 7.30 TC3 > >> UPDATE STATISTICS HIGH for all columns in the WHERE clause. Not enough! Follow the recommendations in the release notes for 7.2 and 7.3 or just get my dostats.ec program which produces optimal stats automatically. Dostats.ec is part of the package utils2_ak from the IIUG Software Repository. > >> No logging for the database. Queries issued using dbaccess. > >> SHMVIRTSIZE = 25MB, DS_TOTAL_MEMORY = 20MB.> >> (Considering the fact that I do only reads, I kept BUFFERS to > >> 200. Is it correct?) NO, you are thrashing buffers. Look at the query plan you are doing sequential scans on three tables and an indexed lookup on the third. Unless the four tables comprise less than 200 pages including index pages the sequential scans, as they progress, are going to flush the pages previously read for the index lookup so they will have to be read again, and not just the index pages but the data pages also, since Informix does not maintain row locality unless the table has a clustered index and has not been modified since clustering, the indexed query of the fourth table will involve accessing the same page repeatedly intermixed with scan page reads to the other three tables, index read on the fourth table and indexed reads of other data pages. With only 200 buffers to work with the data pages of the fourth table will surely be aged out. Look at onstat -p and see your read cache percentage is low. Also look at the number of disk reads vs page reads. Increase the buffers to about 1/3 of physical memory for optimal performance. So if you have 96MB use 14000-16000 buffers. The more buffers the merrier. Informix has the BEST buffer cache in the industry, use it! Fix all this and I would not be surprised if the query took < 00:15. Now if you had additional CPUs and fragmented the tables watch out! IDS will show you tricks that MSSQL never dreamed of! Art S. Kagel