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

curl - PHP fsockopen help required -

i trying work on cloudflare api , have found in documentation. unfortunately have no idea how can use in fsockopen or through curl. here example of post request. need know how can make request below data post using curl or fsockopen curl https://www.cloudflare.com/api_json.html -d 'a=cache_lvl' \ -d 'tkn=8afbe6dea02407989af4dd4c97bb6e25' \ -d 'email=sample@example.com' \ -d 'z=example.com' \ -d 'v=agg' here link cloudflare https://www.cloudflare.com/docs/client-api.html#s4.2 try one: $ch = curl_init("https://www.cloudflare.com/api_json.html"); curl_setopt($ch, curlopt_returntransfer,1); curl_setopt($ch, curlopt_followlocation, 1); curl_setopt($ch, curlopt_ssl_verifypeer,false); // handling of -d curl parameter here. $param = array( 'a' => 'cache_lvl', 'tkn' => '8afbe6dea02407989af4dd4c97bb6e25', 'email' => 'sample@example.com', 'z' =...
Read more

HTTP/1.0 407 Proxy Authentication Required PHP -

i working in restricted network/limited access internet. since have use proxy make web service call. here php code calling web service , response . $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' => $proxy_host, 'proxy_port' => $proxy_port, 'proxy_login' => $proxy_login, 'proxy_password' => $proxy_password)); response is fatal error: uncaught soapfault exception: [wsdl] soap-error: parsing wsdl: couldn't load ' http://www.xyz.com/abc/wsdls/login.wsdl ' : failed load external entity if copy url error message , paste in browser(with same proxy settings) works. 2nd scenario is, if pass values instead of variables giving me diffarant response $client = new soapclient("http: //www.xyz.com/abc/wsdls/login.wsdl", array("trace" => 1, "exceptions" => 1, 'proxy_host' ...
Read more

c# - Resource not found error -

i have problem accessing method on homecontroller. show code of method : [httpget] public actionresult decriptidentifiantetredirige(string login_crypter, string mdp_crypter) { string loginacrypter = _globalmanager.protegemotdepasse(login_crypter); string mdpacrypter = _globalmanager.protegemotdepasse(mdp_crypter); user userapp = new models.user(login_crypter, mdp_crypter); if (userapp.authentificationvalidee(userapp.userlogin, userapp.password)) { session["name"] = userapp.userlogin; return redirecttoaction("accueil", "home"); } else { return redirecttoaction("validerauthentification", "home"); } } then in routeconfig.cs wrote route : routes.maproute( name: "authentificationapresdecryptage", url: "{controller}/{action}/{login_crypter}/{mdp_crypter}", defaults: new...
Read more