Slow DB queries with MS-SQL

Hi,

We are running CI and and MS-SQL however when doing a sql query such as select * from tblTest, it can take over 22 seconds to get a result back (of say 17,000 rows) compared to the same query run in sql studio returning in less than 2 seconds the result.

We are running on windows 2003 server, apache 2.2, php 5.3.5 with microsoft drivers and CI 3.1.0.  MS-SQL is installed on a seperate server but running the MS studio locally or on a client the time difference is still over 20seconds.

Any clues as to what is causing the delay.

Indexes. Somewhere in your query whatever you are trying to search on is not indexed so to find the data it has to read all 17,000 rows. If you set an index on the value you are looking up, it should read much faster.

Side note, even 2 seconds is really slow so there might be another issue too, hard to know for sure without looking at the server.

Indexes. Somewhere in your query whatever you are trying to search on is not indexed so to find the data it has to read all 17,000 rows. If you set an index on the value you are looking up, it should read much faster.

Side note, even 2 seconds is really slow so there might be another issue too, hard to know for sure without looking at the server.

Hi,

I have done a fresh install using wamp and some testing.  What I have found is when using a <table> it is a lot slower to draw as apposed to just echo the data out field after field.  Below is the snippet I quickly did in the view, there is quite a time difference when you take the <table> parts out.

Using WAMP, Codeigniter, Bootstrap and MS-SQL.

Code:

<div class="row buffer-top">
    <div class="col-md-12">
        <table>
                <?php
                    foreach($rows as $row) {
                        echo '<tr>';
                            echo '<td>'.$row->id.'</td>';
                            echo '<td>'.$row->rlProductDescription.'</td>';
                            echo '<td>'.$row->rlARTG.'</td>';
                            echo '<td>'.$row->rlMBatchNo.'</td>';
                            echo '<td>'.$row->rlQuantity.'</td>';
                            echo '<td>'.($row->rlManufacturer=='999999' ? '' : $row->rlManufacturer).'</td>';
                            echo '<td>'.($row->rlPacker=='999999' ? '' : $row->rlPacker).'</td>';
                            echo '<td>'.$row->rlReleaseDate.'</td>';
                            echo '<td>'.($row->rlFirstRelease ? 'Yes' : 'No').'</td>';
                            echo '<td>'.$row->rlPONumber.'</td>';
                        echo '</tr>';
                    }
                ?>
            </table>
    </div>
</div>

The issue is your query as burgoyn1 wrote. Put the query in your DB app and run an explain on it. It's doing full table scans you need to add indices.

Drawing it is a separate issue. The more complex the markup the slower the browser will paint the page. You shouldn't be drawing 17k rows on one page. Paginate it or infinite scroll and load a few hundred at a time.