logo
down
shadow

Execute query command (dynamic sql) runs faster than ordinary query


Execute query command (dynamic sql) runs faster than ordinary query

Content Index :

Execute query command (dynamic sql) runs faster than ordinary query
Tag : sql , By : harley.holt
Date : November 28 2020, 08:01 AM


Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Query runs faster in SQL Server than via ADO.NET command being sent


Tag : chash , By : James Lupiani
Date : March 29 2020, 07:55 AM
Does that help You are returning a blob of the image. SSMS doesn't actually get all the data - it truncates large values of [n][text], [n][varchar](max), image and varbinary(max), since you're usually not directly using those results. It sounds like this is therefore a bandwidth issue. Check the size of the blob - if it is insanely large, this is likely.
Other possibilities for unusual performance:

Which query will execute faster, a query which uses table object or a query which uses temporary table in sql server


Tag : sql-server , By : Fred Morrison
Date : March 29 2020, 07:55 AM
Hope this helps It depends! - as always there are a lot of factors that come into play here.
A table variable tends to work best for small numbers of rows - e.g. 10, 20 rows - since it never has statistics, cannot have indices on it, and the SQL Server query optimizer will always assume it has just a single row of data. If you have too many rows in a table variable, this will badly skew the execution plan being determined.

Google BigQuery: bq query from command line fails, although it runs fine from the UI Query Editor


Tag : google-bigquery , By : Reiner
Date : March 29 2020, 07:55 AM
wish helps you You need to escape any characters in your SQL that the command line is trying to interpret. What I find much easier & quicker is to put my SQL in a file, and pipe it instead. For example:
bq query --destination_table grey-sort-challenge:partitioning_magic.foobarred --use_legacy_sql=false "$(cat data.sql)"

Why is my Sql Query is Faster the Second Time it Runs?


Tag : sql-server , By : Thierry Brunet
Date : March 29 2020, 07:55 AM
it fixes the issue When you run your query for the first time and the data is not in cache, the server read the data from disk. It is time-comsuming. The second time you execute the same query data is already in cache so it requires less time.

Same query runs faster when out of proc


Tag : sql , By : Ansari
Date : March 29 2020, 07:55 AM
To fix this issue We have a specific query that runs a lot slower when inside a proc. I have to add here that it is enclosed within a two level-cursor. However, both cursors have an iteration result-set of one line. , To answer your question:
insert into @tbl_loyal_loop2 (cnt, store, map, pda, insdate, line, item, loyalty_policy_data, loyal_calc, loyalty_policy)
select @cnt, t.store, t.map, t.pda, t.insdate, t.line, t.item, ld.tab_id,  
case @typecalc
    when 1 then convert(bigint,round(isnull(t.valueFromTran,0.00) * ld.value , 0 ) )
    when 2 then convert(bigint,round(isnull(t.netvalue,0.00) * ld.value , 0 ) )
    when 3 then convert(bigint,isnull(t.qty,0) * ld.value )
    when 4 then convert(bigint,round(isnull(t.valueFromPrice2,0.00) * ld.value , 0 ) )
    when 5 then convert(bigint,round(isnull(t.valueFromPrice3,0.00) * ld.value , 0 ) )
    when 6 then convert(bigint,round(isnull(t.valueFromPrice4,0.00) * ld.value , 0 ) )
else 0 end
,@loop2_loyalty_policy
from loyalty_policy_data ld -- with (index=ind_loyalty_policy_02)
            inner join #tbl_data t on t.insdate >= ld.fdateactive and t.insdate <= ld.tdateactive
where ld.loyalty_policy = @loop2_loyalty_policy 
and ld.tdateactive >= @from_rundate and ld.fdateactive <= @to_rundate
and t.dbupddate > @loop1_dbupddate  
and
    case when @tpdim1 is null then '' 
    else  
            case  @tpdim1 
                when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                when 'CUSTOMER'         then @customer
            else '' end
    end
    = case when @tpdim1 is null then '' else ld.dim1 end
and 
    case when @tpdim2 is null then '' 
    else  
            case  @tpdim2 
                when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                when 'CUSTOMER'         then @customer                     
            else '' end
    end
    = case when @tpdim2 is null then '' else ld.dim2 end
OPTION(RECOMPILE);
and
    case when @tpdim1 is null then '' 
    else  
            case  @tpdim1 
                when 'STORE'            then t.store when 'BRAND' then t.brand  when 'CAT1' then t.cat1   when 'CAT2' then t.cat2   when 'CAT3' then t.cat3   when 'ITEM' then t.item    
                when 'CUSTGROUP'  then t.custgroup when 'CUSTGROUP2' then t.custgroup2 when 'CUSTGROUP3' then t.custgroup3
                when 'CUSTOMER'         then @customer
            else '' end
    end
    = case when @tpdim1 is null then '' else ld.dim1 end
and
(
    @tpdim1 is null
    OR
    (
            ld.dim1 =
            case @tpdim1
                when 'STORE'      then t.store 
                when 'BRAND'      then t.brand  
                when 'CAT1'       then t.cat1   
                when 'CAT2'       then t.cat2   
                when 'CAT3'       then t.cat3   
                when 'ITEM'       then t.item    
                when 'CUSTGROUP'  then t.custgroup 
                when 'CUSTGROUP2' then t.custgroup2 
                when 'CUSTGROUP3' then t.custgroup3
                when 'CUSTOMER'   then @customer
                else ''
            end
    )
)
and
(
    ld.dim1 = `EL0134366`
)
Related Posts Related QUESTIONS :
  • SQL query for a database scheme
  • Timer-based event triggers
  • SQL query, count and group by
  • Paging SQL Server 2005 Results
  • SQL Server 2005 For XML Explicit - Need help formatting
  • How do I use T-SQL Group By
  • How do I split a string so I can access item x?
  • Date Condition with Case when Holiday exclude
  • SQL Query Select from 1 table and return data based on 2 columns
  • how to use rank/join and where together
  • How to create new column with values counting up every 9th value with SQL?
  • Most frequent combination of words/characters SQL Server
  • Why can't I access a field defined as "Select 1" from a subquery in the outer query?
  • How to solve Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type int.?
  • UPDATE row inserted earlier in CTE
  • Display current month values based upon abbreviation (Oct) SQL Server
  • SQL Modify returned select value without union
  • How to put a variable in a like statement in vba?
  • More Elegant way to Batch Validate Phone Numbers
  • SQL find parent where children match over multiple rows
  • A combo box that has to be filter the same data in that field
  • Exclude updated record from trigger function
  • Insert into table from select only when select returns valid rows
  • How to fix SQLite3::SQLException: near "to": syntax error: SELECT "".* FROM "" WHERE "
  • Filter msdb.dbo.sysjobsteps.command into seperate columns
  • How to extract year or month,day from oracle function without using sub-query?
  • Reset sum when condition is met in Oracle
  • Date Between (Start & Now)
  • Reset running sum when condition is met in Oracle
  • Why is my date criteria filtering incorrectly in a MsysObjects query using DCount?
  • Stripping date/time delimiters in an SSIS csv export
  • SSIS Error on insertion - Error when new primary keys in Dimension table
  • How can I make a temp table of specific datetime data within a range?
  • AnalysisException: Syntax error in SQL After multiple join
  • SQL Query Multiple Columns Using Distinct on One Column Only and Using Order By
  • Sum inventory item groups
  • TSQL: Parsing strings with various characters
  • Using SQL Server : how to use select criteria based on sum
  • Inserting dynamic pivot Result into Temp table
  • Joining multiple fields in a dataset joined with descriptions
  • Conditional WHERE EXISTS in Oracle query
  • How to get the count of each unique value in a column between two dates?
  • SQL: Select only if multiple values are IN 1 column from CatalogTable
  • In a query (no editing of tables) how do I join data without any similarities?
  • Create nested json blobs in PostgreSQL
  • Getting extra dates while using cte
  • Way to alter one table if another was altered
  • Proper use of semicolons in functions and procedures
  • Cursor Not looping all the Records
  • Select inner join returns wrong result
  • Group By showing duplicates
  • Error in using subquery in access join query
  • How do I use a composite pk in a stored procedure?
  • Oracle Unique Indexes
  • Spooling data to CSV truncates
  • How do views reduce code duplication?
  • SQL: Gather right hand values from a join
  • SQL Agent Logon - What is going on?
  • How do I combine two rows of same part, but add quantities?
  • How to optimize this SQL query for a rectangular region?
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com