• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PQ/ODBC to pull database tables

Hi all Gurus!

We have a pervasive SQL server, and I use ODBC to connect to our pervasive SQL database all the time. One of the tables had 56 fields, most of which I would remove after the navigation step, and the data loaded pretty quickly. I kept date and number fields, and a couple of fields with 3-5 text characters. On occasion, I would also pull a description field, which was split into 20 different fields (CHAR type), which I would combine into one. When I needed description, the report took longer to run than when I didn't (still usually less than 5 minutes). I assumed it was because there were 20 more fields it needed to process when running. So I only ran it when I needed to. When running without the description, it would process at like 100k-150k rows per second; it was more like 10k-15k when adding the description.

Both the version of our software and the pervasive SQL server were updated at the end of June, and with the upgrade, the description field was now only a single field. It's a completely different table I pull from than it was before (only 35 fields) but has the same info; however, now when I pull the equivalent fields and pull the description field as well, it's processing at about 1k rows per second. There are, as of today, a little more than 2.4 million rows in this particular table, and often times, it processes double the rows for whatever reason. So at 1k rows per second, it takes a really long time to pull, and it isn't really manageable.

For reference, the field type is LONG VARCHAR, which looks like it holds up to 32,700 characters. Though it's totally a guess, I assume it's having to process 32,000 characters per row (x 2.4 million rows), even if there aren't 32k characters in the field. If that is the case, would there be any way on the front end to have it process only the number of characters in the field as opposed to the limit of characters? I spoke with the tech support for the software that uses the pervasive, and they didn't have any ideas as to how to make the report run faster, so I thought I would reach out on this forum to see if anyone had any thoughts on how to get the report to run faster, or at the very least to confirm that there isn't a way.

I hope all that made sense. Please let me know if there is anything I can provide that may be helpful. Since it didn't directly impact a file, I wasn't sure what I could provide.

Thanks so much in advance!