Some days ago I ran into problem where complexity of query became prominent. The query used to return 60000 rows after filters and has to compare them against "IN" block of query which again containing around 60000 keys in them. You can see it is an N^2 complexity.
So the first solution I ran into was to pass 60000 keys to the database. As Java being my (so called sophasticated) language that I am using, I have to use jdbc connectors. As jdbc connectors has limitation of passing only 1000 parameters, it is my problem to tackle with. At this point I was still hoping 60000^2 would be pretty fast on DB side. Coming back to parameters issue, I first used Query.setParameterList(Collection), but failed miserable.
Moving forward and identifying problem decided not to use Query.setParameterList(Collection). However still hoping 60000^2 would work once I can pass the parameters. Now to hack the jdbc a bit, I requested the query string first with a patters in it. The patter would be replaced by 60000 keys which is generated by java program. After replacing the string,query sent to database to do the comparison and I waited ........ Did not come back. ALAS.
An interesting discussion with my colleague suggested that processing data on java would be a better idea. I fastened my seat belts for this experiment. I did the same query but this time just returned all the rows to app side. App was comparing the results, a bit slow but much much faster than SQL query.
Sharing in the benefits of those who believe in getting things done .....
How to use setParameterList
JAVA Link
No comments:
Post a Comment