sql server - Query slowdown when joining small table -


i've hit strange problem. have query uses 4 tables (sorry latvian names):

  • kl_precesizejvielas: 20 rows
  • parvietots_details: 27897 rows
  • razots: 282 rows
  • kl_simple: 25 rows

tables have clustered index (primary key), no other indexes.

i'm executing query: 

select  kl_precesizejvielas.apraksts precenosauk ,         count(*) razosanuskaits ,         sum(skaits) kopskaits ,         mervienibas.apraksts mervieniba    kl_simple mervienibas         inner join ( kl_precesizejvielas                      inner join ( parvietots_details                                   inner join razots on parvietots_details.id_parvietots_master = razots.id_preces_ieks_kust_id                                 ) on kl_precesizejvielas.id = parvietots_details.id_prece_izejviela                    ) on mervienibas.id = kl_precesizejvielas.default_mervieniba   razots.id_atbildpersona = 27         , razots.datumslaiks >= ( select top 1                                             datums                                        loginhistory                                       id_user = 27                                     order datums desc                                   ) group kl_precesizejvielas.apraksts ,         mervienibas.apraksts order kl_precesizejvielas.apraksts ,         mervienibas.apraksts

here execution plan: exec plan link bigger picture

the query takes 6 seconds process! execution plan shows biggest work done when joining big parvietots_details table kl_precesizejvielas , done before applying filter on "razots". seems wrong.

the strange thing is, if take away small 25-row table kl_simple/mervienibas execution time 126 milliseconds. big table joined after applying filter on "razots" , think makes difference (?)

select  kl_precesizejvielas.apraksts precenosauk ,         count(*) razosanuskaits ,         sum(skaits) kopskaits    kl_precesizejvielas         inner join ( parvietots_details                      inner join razots on parvietots_details.id_parvietots_master = razots.id_preces_ieks_kust_id                    ) on kl_precesizejvielas.id = parvietots_details.id_prece_izejviela   razots.id_atbildpersona = 27         , razots.datumslaiks >= ( select top 1                                             datums                                        loginhistory                                       id_user = 27                                     order datums desc                                   ) group kl_precesizejvielas.apraksts order kl_precesizejvielas.apraksts

exec plan link bigger picture

what cause of slowdown? should rewrite query? why query processor chooses such plan?

i tried dropping statistics, there no improvement.

edit: here execution plan xmls:
slow query
fast query

its difficult read screenshot/sql because of size/language. looks if way structuring join kl_simple means rest of query being repeated every row in kl_simple.


Comments

Post a Comment

Popular posts from this blog

php - get table cell data from and place a copy in another table -

two-part question: have 2 tables: 1 table items , other table selected items copied when add button clicked. after, when items wanted selected, there 'finished' button post items selected db. i started javascript didn't far @ because jquery seems better suited i'm not familiar it function addto() { var div = document.getelementbyid('fixeddiv','inside'); div.innerhtml = div.innerhtml + 'item added'; } <div id='fixeddiv'> <table align="center" id="tradee"> <th rowspan="2"> other item</th> <?php while($rowi =$item->fetch_assoc()) {?> <tr> <td> <?php echo $rowi['item_name']; ?> </td> <td><?php echo $rowi['item_description'];?></td> </tr> <?php } ?> </table> <br> <table align="center" id="tradetable"
Read more

javascript - Mootools wait with Fx.Morph start -

i trying var effect = new fx.morph(testmorph, { wait/delay 2 seconds before starting. ( fiddle here ) but when try .wait(2000) or .delay(2000) , or .wait(2000, effect) uncaught typeerror: object [object object] has no method 'delay' any ideas how working? code i'm using: var testmorph = document.id('testmorph'); var effect = new fx.morph(testmorph, { transition: 'back:out', duration: 900, link: 'chain' }).start({ 'top': 20, 'opacity': 1 }).start({ 'border-color': '#a80025', 'color': '#a80025' }); effect.delay(2000); you can use combination of chain() , delay() achieve desired effect. new fx.morph(testmorph, { transition: 'back:out', duration: 900, link: 'chain' }).start().chain(function(){ this.start.delay(2000,effect,{ //first }); }).chain(function(){ this.start({ //second }); }); the
Read more

php - Navigate throught databse rows -

i run image hosting site, , ive run problem. want make 2 buttons beside pic "next image" , "prev image". im new php/mysql , haveing troubles. check out got. use id (numbers) find correct row, have image name build next link. how make below query next , prev image names + ids? $query_next = ("select imageid, image_name images image_name = '$image_main' order imageid desc limit 1"); $query_prev = ("select imageid, image_name images image_name = '$image_main' order imageid asc limit 1"); // next $next = mysql_query($query_next); // prev $prev = mysql_query($query_prev); while ($row = mysql_fetch_assoc($next)) { $next = $row['image_name']; } while ($row = mysql_fetch_assoc($prev)) { $prev = $row['image_name']; } the html = > simple stuff. <a href="http://mysite.com/view_image/<?=$next?>">next image</a> <a href="http://mysite.com/view_image/<?=$pr
Read more