Databases
StackOverflow2013
Postsdbo
POImproving performance of SQL next row
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POImproving performance of SQL next row
    primarykey
    data
    text
    <p>I wrote an application that performs around 40 queries and then does some processing on the results of each query. (Right now I'm using Qt 3.2.2 in Visual C++ 6.0 on Windows XP with SQL Server 2005, but that's not required.) The paradigm is to create a <code>QSqlQuery</code> object with the query (this causes the query to be performed) and then <code>while (query.next()) { operate(query.value(0)); }</code> By profiling I find that the <code>query.next()</code> call is taking up half the time of the program, which seems excessive as it's just fetching a row of data (6 or 7 fields).</p> <p>This performance is unacceptable, and I'm looking for a way to improve this. I'm open to changing anything -- switching my compiler, switching languages, switching the paradigm I use to get data from the database. How can I speed this up?</p> <p>Here's the query:</p> <pre class="lang-sql prettyprint-override"><code>select rtrim(Portfolio.securityid), rtrim(type), rtrim(coordinate), rtrim(value) from MarketData inner join portfolio on cast(MarketData.securityid as varchar(36))=portfolio.securityid where Portfolioname=? and type in ('bond_profit', 'bondoption_profit', 'equity_profit', 'equityoption_profit') and marketdate=? order by Portfolio.securityid, type, coordinate </code></pre> <p>CPU usage is around 40% while the program is running, so I suspect it's spending the majority of its time waiting for the .next() call to return with more data.</p> <p>Performing the same query in SSMS returns 4.5 million rows in about 5 minutes, but the total time spent waiting on .next() during the run of the program is 30 minutes.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAnotherParker
    1. USAnotherParker
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COI imagine it's waiting on the query to actually process on the server, not waiting for your local code to execute...
      singulars
      1. POImproving performance of SQL next row
      1. USJNK
    2. COwhat do you mean by "actually process"? Do you think the query is so complicated that the server just isn't able to cough up rows that fast?
      singulars
      1. POImproving performance of SQL next row
      1. USAnotherParker
    3. COI think that it's likely that the time spent for your app to connect to SQL, send the query, have SQL process the query, and return the data row is probably longer than it takes your app to be ready for the next row
      singulars
      1. POImproving performance of SQL next row
      1. USJNK
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload