{"id":254,"date":"2015-03-13T11:17:28","date_gmt":"2015-03-13T11:17:28","guid":{"rendered":"http:\/\/danny.janlov.se\/?p=254"},"modified":"2021-05-20T06:22:41","modified_gmt":"2021-05-20T06:22:41","slug":"prestandatips-for-sql-integrationer-i-inobiz","status":"publish","type":"post","link":"https:\/\/danny.janlov.se\/?p=254","title":{"rendered":"Prestandatips f\u00f6r sql-integrationer i Inobiz"},"content":{"rendered":"<p>T\u00e4nkte dela med mig av lite l\u00e4rdomar om Inobiz n\u00e4r det g\u00e4ller prestandaoptimering som jag l\u00e4rt mig genom \u00e5ren.<\/p>\n<ol>\n<li>Om du har tunga select-satser mot stora (och transaktionsintensiva tabeller) som du beh\u00f6ver iterera igenom se d\u00e5 till att anv\u00e4nda <strong>WITH (READUNCOMMITTED)<\/strong> om m\u00f6jligt eller \u00e4nnu b\u00e4ttre skapa en temp-tabell som du l\u00e4ser ifr\u00e5n. Dock finns det en bugg i ODBC-drivrutinen <strong>SQL Server Native Client 11.0 ODBC<\/strong> som ger felmeddelande om att man inte kan sl\u00e5 upp kolumntypen och f\u00f6r att komma runt detta f\u00e5r man just nu anv\u00e4nda sig av permanenta temptabeller ist\u00e4llet f\u00f6r br\u00e4dg\u00e5rdsvarianten.<br \/>\n<a href=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/SkapaTemptabeller.jpg\"><img data-attachment-id=\"258\" data-permalink=\"https:\/\/danny.janlov.se\/?attachment_id=258\" data-orig-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/SkapaTemptabeller.jpg\" data-orig-size=\"1783,97\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;}\" data-image-title=\"SkapaTemptabeller\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/SkapaTemptabeller-300x16.jpg\" data-large-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/SkapaTemptabeller-1024x55.jpg\" decoding=\"async\" loading=\"lazy\" class=\"alignnone size-medium wp-image-258\" alt=\"SkapaTemptabeller\" src=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/SkapaTemptabeller-300x16.jpg\" width=\"300\" height=\"16\" srcset=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/SkapaTemptabeller-300x16.jpg 300w, https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/SkapaTemptabeller-1024x55.jpg 1024w, https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/SkapaTemptabeller-500x27.jpg 500w, https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/SkapaTemptabeller.jpg 1783w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/li>\n<li>F\u00f6r stora mappningar med m\u00e5nga niv\u00e5er kan det vara mycket effektivare att f\u00f6rst omvandla till ett flatfilsformat i sql som mellanformat och sedan anv\u00e4nda flatfilen som k\u00e4lla ist\u00e4llet d\u00e5 Inobiz kommer g\u00f6ra en select f\u00f6r varje rad.<br \/>\n<a href=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/MellanformatFranTemptabell.jpg\"><img data-attachment-id=\"257\" data-permalink=\"https:\/\/danny.janlov.se\/?attachment_id=257\" data-orig-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/MellanformatFranTemptabell.jpg\" data-orig-size=\"1132,700\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;}\" data-image-title=\"MellanformatFranTemptabell\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/MellanformatFranTemptabell-300x185.jpg\" data-large-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/MellanformatFranTemptabell-1024x633.jpg\" decoding=\"async\" loading=\"lazy\" class=\"alignnone size-medium wp-image-257\" alt=\"MellanformatFranTemptabell\" src=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/MellanformatFranTemptabell-300x185.jpg\" width=\"300\" height=\"185\" srcset=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/MellanformatFranTemptabell-300x185.jpg 300w, https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/MellanformatFranTemptabell-1024x633.jpg 1024w, https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/MellanformatFranTemptabell-485x300.jpg 485w, https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/MellanformatFranTemptabell.jpg 1132w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><br \/>\n<a href=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/FlatfiltillPricat.jpg\"><img data-attachment-id=\"255\" data-permalink=\"https:\/\/danny.janlov.se\/?attachment_id=255\" data-orig-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/FlatfiltillPricat.jpg\" data-orig-size=\"1084,1025\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;}\" data-image-title=\"FlatfiltillPricat\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/FlatfiltillPricat-300x283.jpg\" data-large-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/FlatfiltillPricat-1024x968.jpg\" decoding=\"async\" loading=\"lazy\" class=\"alignnone size-medium wp-image-255\" alt=\"FlatfiltillPricat\" src=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/FlatfiltillPricat-300x283.jpg\" width=\"300\" height=\"283\" srcset=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/FlatfiltillPricat-300x283.jpg 300w, https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/FlatfiltillPricat-1024x968.jpg 1024w, https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/FlatfiltillPricat-317x300.jpg 317w, https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/FlatfiltillPricat.jpg 1084w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/li>\n<li>Undvik i st\u00f6rsta m\u00f6jliga m\u00e5n att g\u00f6ra ytterligare select-satser under en select i Inobiz d\u00e5 detta i vissa fall med h\u00f6g belastning kan resultera att den underordnade select-satsen inte k\u00f6rs. Detta g\u00e4ller speciellt block som repeteras.<br \/>\n<a href=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/subselect.jpg\"><img data-attachment-id=\"259\" data-permalink=\"https:\/\/danny.janlov.se\/?attachment_id=259\" data-orig-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/subselect.jpg\" data-orig-size=\"845,548\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;}\" data-image-title=\"subselect\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/subselect-300x194.jpg\" data-large-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/subselect.jpg\" decoding=\"async\" loading=\"lazy\" class=\"alignnone size-medium wp-image-259\" alt=\"subselect\" src=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/subselect-300x194.jpg\" width=\"300\" height=\"194\" srcset=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/subselect-300x194.jpg 300w, https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/subselect-462x300.jpg 462w, https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/subselect.jpg 845w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><\/li>\n<li>\u00c4r mappningen mot t.ex. ett EDIFACT-format s\u00e5 kan man f\u00f6rb\u00e4ttra prestandan genom att inaktivera segment som man inte anv\u00e4nder.<br \/>\n<a href=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/InaktiveraSegment.jpg\"><img data-attachment-id=\"256\" data-permalink=\"https:\/\/danny.janlov.se\/?attachment_id=256\" data-orig-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/InaktiveraSegment.jpg\" data-orig-size=\"180,314\" data-comments-opened=\"1\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;}\" data-image-title=\"InaktiveraSegment\" data-image-description=\"\" data-image-caption=\"\" data-medium-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/InaktiveraSegment-171x300.jpg\" data-large-file=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/InaktiveraSegment.jpg\" decoding=\"async\" loading=\"lazy\" class=\"alignnone size-medium wp-image-256\" alt=\"InaktiveraSegment\" src=\"https:\/\/danny.janlov.se\/wp-content\/uploads\/2015\/03\/InaktiveraSegment-171x300.jpg\" width=\"171\" height=\"300\" \/><\/a><\/li>\n<\/ol>\n<p>\/Danny<\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon-text sd-sharing\"><h3 class=\"sd-title\">Dela detta:<\/h3><div class=\"sd-content\"><ul><li><a href=\"#\" class=\"sharing-anchor sd-button share-more\"><span>Dela<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><div class=\"sharing-hidden\"><div class=\"inner\" style=\"display: none;\"><ul><li class=\"share-facebook\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-facebook-254\" class=\"share-facebook sd-button share-icon\" href=\"https:\/\/danny.janlov.se\/?p=254&amp;share=facebook\" target=\"_blank\" title=\"Klicka f\u00f6r att dela p\u00e5 Facebook\" ><span>Facebook<\/span><\/a><\/li><li class=\"share-twitter\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-twitter-254\" class=\"share-twitter sd-button share-icon\" href=\"https:\/\/danny.janlov.se\/?p=254&amp;share=twitter\" target=\"_blank\" title=\"Klicka f\u00f6r att dela p\u00e5 Twitter\" ><span>Twitter<\/span><\/a><\/li><li class=\"share-end\"><\/li><li class=\"share-linkedin\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-linkedin-254\" class=\"share-linkedin sd-button share-icon\" href=\"https:\/\/danny.janlov.se\/?p=254&amp;share=linkedin\" target=\"_blank\" title=\"Klicka f\u00f6r att dela via LinkedIn\" ><span>LinkedIn<\/span><\/a><\/li><li class=\"share-email\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"\" class=\"share-email sd-button share-icon\" href=\"mailto:?subject=%5BDelat%20inl%C3%A4gg%5D%20Prestandatips%20f%C3%B6r%20sql-integrationer%20i%20Inobiz&body=https%3A%2F%2Fdanny.janlov.se%2F%3Fp%3D254&share=email\" target=\"_blank\" title=\"Klicka f\u00f6r att e-posta en l\u00e4nk till en v\u00e4n\" data-email-share-error-title=\"Har du e-post konfigurerat?\" data-email-share-error-text=\"Om du har problem med att dela via e-post \u00e4r det m\u00f6jligt att e-post inte \u00e4r konfigurerat f\u00f6r din webbl\u00e4sare. Du kan beh\u00f6va skapa en ny e-post sj\u00e4lv.\" data-email-share-nonce=\"e9d713d2c0\" data-email-share-track-url=\"https:\/\/danny.janlov.se\/?p=254&amp;share=email\"><span>E-post<\/span><\/a><\/li><li class=\"share-end\"><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div><\/div><\/div>","protected":false},"excerpt":{"rendered":"<p>T\u00e4nkte dela med mig av lite l\u00e4rdomar om Inobiz n\u00e4r det g\u00e4ller prestandaoptimering som jag l\u00e4rt mig genom \u00e5ren. Om du har tunga select-satser mot stora (och transaktionsintensiva tabeller) som du beh\u00f6ver iterera igenom se d\u00e5 till att anv\u00e4nda WITH &hellip; <a href=\"https:\/\/danny.janlov.se\/?p=254\">L\u00e4s mer <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon-text sd-sharing\"><h3 class=\"sd-title\">Dela detta:<\/h3><div class=\"sd-content\"><ul><li><a href=\"#\" class=\"sharing-anchor sd-button share-more\"><span>Dela<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><div class=\"sharing-hidden\"><div class=\"inner\" style=\"display: none;\"><ul><li class=\"share-facebook\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-facebook-254\" class=\"share-facebook sd-button share-icon\" href=\"https:\/\/danny.janlov.se\/?p=254&amp;share=facebook\" target=\"_blank\" title=\"Klicka f\u00f6r att dela p\u00e5 Facebook\" ><span>Facebook<\/span><\/a><\/li><li class=\"share-twitter\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-twitter-254\" class=\"share-twitter sd-button share-icon\" href=\"https:\/\/danny.janlov.se\/?p=254&amp;share=twitter\" target=\"_blank\" title=\"Klicka f\u00f6r att dela p\u00e5 Twitter\" ><span>Twitter<\/span><\/a><\/li><li class=\"share-end\"><\/li><li class=\"share-linkedin\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"sharing-linkedin-254\" class=\"share-linkedin sd-button share-icon\" href=\"https:\/\/danny.janlov.se\/?p=254&amp;share=linkedin\" target=\"_blank\" title=\"Klicka f\u00f6r att dela via LinkedIn\" ><span>LinkedIn<\/span><\/a><\/li><li class=\"share-email\"><a rel=\"nofollow noopener noreferrer\" data-shared=\"\" class=\"share-email sd-button share-icon\" href=\"mailto:?subject=%5BDelat%20inl%C3%A4gg%5D%20Prestandatips%20f%C3%B6r%20sql-integrationer%20i%20Inobiz&body=https%3A%2F%2Fdanny.janlov.se%2F%3Fp%3D254&share=email\" target=\"_blank\" title=\"Klicka f\u00f6r att e-posta en l\u00e4nk till en v\u00e4n\" data-email-share-error-title=\"Har du e-post konfigurerat?\" data-email-share-error-text=\"Om du har problem med att dela via e-post \u00e4r det m\u00f6jligt att e-post inte \u00e4r konfigurerat f\u00f6r din webbl\u00e4sare. Du kan beh\u00f6va skapa en ny e-post sj\u00e4lv.\" data-email-share-nonce=\"e9d713d2c0\" data-email-share-track-url=\"https:\/\/danny.janlov.se\/?p=254&amp;share=email\"><span>E-post<\/span><\/a><\/li><li class=\"share-end\"><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div><\/div><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true},"categories":[1],"tags":[22,23,24,25],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/pYHHf-46","_links":{"self":[{"href":"https:\/\/danny.janlov.se\/index.php?rest_route=\/wp\/v2\/posts\/254"}],"collection":[{"href":"https:\/\/danny.janlov.se\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/danny.janlov.se\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/danny.janlov.se\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/danny.janlov.se\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=254"}],"version-history":[{"count":2,"href":"https:\/\/danny.janlov.se\/index.php?rest_route=\/wp\/v2\/posts\/254\/revisions"}],"predecessor-version":[{"id":313,"href":"https:\/\/danny.janlov.se\/index.php?rest_route=\/wp\/v2\/posts\/254\/revisions\/313"}],"wp:attachment":[{"href":"https:\/\/danny.janlov.se\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=254"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/danny.janlov.se\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=254"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/danny.janlov.se\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=254"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}