| dant@avi.com 2006-03-16, 11:25 am |
| Can somebody shed some insight on the following problem?
I am trying to update a column in a table that has 11
million rows. Let's call this Table1. Table1 contains
NetFlow kind of data. The column I am trying to update is
called HotIP and is of type bit. I have a look-up table
called HotIPs that have the following fields: StartIP
(UNSIGNED INT), EndIP (UNSIGNED INT), ORGANIZATIONAL (bit)
and NOTES (VARCHAR 255).
Now in Table1 I have a SrcIP column that has 232,092 unique
values. I also have a DestIP column that has 1,146,014
unique values. Running the following query:
Update Table1 Set HotIP = Select count(*) from HotIPs WHERE
ORGANIZATIONAL=1 AND SrcIP >= StartIP AND SrcIP <= EndIP
The time it takes to run this update is 191.728 seconds - ~3
minutes
If I now run the same query replacing SrcIP with DestIP:
Update Table1 Set HotIP = Select count(*) from HotIPs WHERE
ORGANIZATIONAL=1 AND DestIP >= StartIP AND DestIP <= EndIP
This query takes 5184.889 - ~ 1.5 Hours.
I should let you know that I have HG (high group) indexes on
SrcIP and DestIP in Table1 and I also have HG indexes on
StartIP and EndIP columns in my HotIPs table. I am in the
process of trying different types of indexes such as HNG for
the StartIP and EndIP columns of the HotIPs table, but that
appears to not be fairing any better. I understand that my
number of unique DestIPs is about 5 times greater than my
number of unique SrcIPs. Can someone tell me why the query
time as a result is orders of magnitude worse? I should
also let you know that the query is running on a server with
8 gigs of RAM. And I have allocated 3000MB to both the MAIN
and TEMP Memory buffers (just to be sure it was not an issue
of the memory allocated to IQ being to little). The number
of rows in the HotIPs table is only 2116 rows.
Any help would be greatly appreciated. Please post and/or
email me at dant@avi.com.
Thanks
|