topic Re: Slow performance for SQL query output tables... in Data Management Questions

Slow performance for SQL query output tables...

JamesDickson — Wed, 04 Dec 2013 07:25:52 GMT

I am attempting to create a tool that queries a large geodatabase table based upon user input, converts the output table to a shapefile and adds the shapefile to the Table Of Contents.

While I have successfully generated this tool (it works fine for small database files), the temporary output table from the SQL query responds slower and slower to any successive operation as a function of how large the original geodatabase table is. The query itself acts very fast, but the table it produces becomes unusable when it is derived from a sizeable source (millions of rows). The output table itself could have very few rows, but if the original database was large, the output table struggles.

This happens no matter what tool I use (Make Query Table, Table To Table, etc.), as long as that tool includes an SQL query. And the issue is not related to the successive tool that acts upon the output table. Even if I add the temporary SQL output table to my TOC, it cannot even be opened (it just spins and spins and spins). This behavior happens whether I use Model Builder, a python script, or just running the tools individually.

My instinct is that this is some sort of memory allocation issue, but it's not obvious to me how exactly to diagnose the problem. Any help would be sincerely appreciated.

Re: Slow performance for SQL query output tables...

VinceAngelo — Wed, 04 Dec 2013 10:55:36 GMT

Your problem description is unclear. How long do the SQL queries take to execute (in seconds)?
How large are the resulting shapefiles (in rows, and Mb)? What is your tool doing (source)?
Are you repopulating the contents with every click? What RDBMS source is in use? Why can't
you use the SQL constraint as a query definition directly from database? (shapefiles corrupt
SQL data upon download and cleanup is far uglier)

- V

Re: Slow performance for SQL query output tables...

JamesDickson — Wed, 04 Dec 2013 11:22:10 GMT

Thanks for the reply.

The SQL queries take just a second or two. The resulting tables are not big at all... maybe 5000 rows, if that. But they are extracted from a database of ~600M rows, which is stored in a file geodatabase.

It might be more clear if I separate my problem from my tool. This problem occurs even when I run the SQL query in isolation. No matter how I achieve the SQL query (Make Query Table, Table To Table, etc.), the resulting table chugs and chugs (I've tried this on multiple machines, both running 10.1). So the problem appears long before I get to the stage where it gets converted to a shapefile.

I hope this information helps. Thanks again.

Re: Slow performance for SQL query output tables...

VinceAngelo — Wed, 04 Dec 2013 11:40:48 GMT

600M is about 580M more than I would ever place in a file geodatabase (or an enterprise
geodatabase, for that matter).

Do you have index(es) on your query terms? Does the query really complete, or does
it just stop returning rows?

- V