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) 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;> > 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

In article <7br68i$41f$1@news.xmission.com>, Octav Chiriac <com@netinfo- moldova.com> writes > >1. I would try increasing the BUFFERS (I would start with at least 5000) Agreed. >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 ? Set OPCOMPIND=0 in the ONCONFIG file. The problem is that this is not using an index. >... > >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. I would do both testing with transaction logging turned off.. >> >> For most of the queries the results are normal (IDS 5-12 times faster, >> with far less memory usage). With one exception: Yes! >> >> 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.ord>er_num >> and items.stock_num=stock.stock_num and items.manu_code=stock.manu_code >> group by customer.company; >> Check indexes exist on customers(num_customer_num) orders(customer_num,order_num) items(order_num,stock_num,manu_code) stock(stock_num,manu_code) >> 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 Sounds like indexes are missing or OPTCOMPIND=2. >> >> 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?) Should be OK, since a single query does not access the same data pages repeatedly (working set is <200 pages). >> >> Thanks in advance >> Sebastian Paul A. >> E-mail: proteus@romus.com > -- David Williams