SQL queries have become extremely slow compared to nearly identical queries on the same tables
Hi all. I've been drafting a SQL query from within MS SSMS, as a .SQL file, and then running it from within SSMS, on the same system that is running the 2019 developer edition server. The query file contains around 100 or so queries, virtually all UPDATE queries, which populate rows in a SUMMARYTABLE with results taken from the WORKTABLE. I've noticed that recently, the last 10% or so of these queries have become exceptionally slow - around 500 times slower than comparable queries. Here is an example of one of the fast ones, and one of the slow ones. The first takes less than a second: UPDATE database1.dbo.SUMMARYTABLE SET database1.dbo.SUMMARYTABLE.SalesTierCount = (select count(*) from database1.dbo.WORKTABLE as T1 WHERE T1.Manager = database1.dbo.SUMMARYTABLE.Manager and T1.SalesTier =1 and T1.Zdate < database1.dbo.SUMMARYTABLE.Zdate) the second one takes about seven minutes: UPDATE database1.dbo.SUMMARYTABLE SET database1.dbo.SUMMARYTABLE.LifetimeCount = (select count(*) from database1.dbo.WORKTABLE as T1 WHERE T1.retailer = database1.dbo.SUMMARYTABLE.retailer and T1.SalesTier =1 and T1.Zdate < database1.dbo.SUMMARYTABLE.Zdate) Does anyone have any suggestions for rectifying this extremely slow issue? 2 of the 3 WHERE/AND clauses are identical. The third clause differs, however both queries query the same type of data for that clause - both are varchar(50), in both tables. This is a link to the execution plan for both queries: https://www.brentozar.com/pastetheplan/?id=rypvD7Zb3 The final part of both plans shows an INDEX SCAN (non-clustered). The first, fast query, has the "Number of rows read" of around 200,000, the second, around 436 million. The total time to run the entire .SQL query has blown out from a minute or so, to nearly 4 hours! Any suggestions greatly appreciated.View is taking more time and consuming 100% cpu utilization
We are using views on tables having huge records to display some information in our application but it is consuming 100% CPU utilization and taking more time to process the records. SELECT COUNT(*) FROM FRA_ITEM_FIELD_DEF--44 SELECT COUNT(*) FROM FRA_STAT_ITEM--59635520(59M) SELECT COUNT(*) FROM FRA_STAT_FIELD--536719680(536M) Available Indexes: FRA_STAT_ITEM: CREATE UNIQUE INDEX FRA_STITM_U1 ON FRA_STAT_ITEM (ID_ORIGINAL,ITEM_TYPE,SEQUENCE_NO) GO CREATE INDEX FRA_STITM_U2 ON FRA_STAT_ITEM (REFERENCE) GO FRA_STAT_FIELD: CREATE INDEX FRA_STFLD_OPT1 ON FRA_STAT_FIELD (ID_STAT_ITEM,ID_FIELD_DEF,TEXT_VALUE,DATETIME_VALUE,NUMBER_VALUE,DECIMAL_VALUE) GO FRA_STAT_FIELD: CREATE INDEX FRA_STFLD_OPT2 ON FRA_STAT_FIELD (ID_FIELD_DEF) INCLUDE (ID_STAT_ITEM,DATETIME_VALUE) GO CREATE INDEX FRA_STFLD_OPT3 ON FRA_STAT_FIELD (ID_FIELD_DEF) INCLUDE (ID_STAT_ITEM,TEXT_VALUE) GO Reported View: CREATE OR ALTER VIEW FRAUD_ALERT_STAT_HIT_VIEW AS SELECT DISTINCT STAT.ID AS ID, STAT.COMPANY AS COMPANY, STAT.TEXT_VALUE AS ACCOUNT, STAT.ITEM_TYPE AS FRAUD_ITEM_TYPE, STAT.DATETIME_VALUE AS VALUE_DATE, STAT.CREATION_TIME AS CREATION_TIME, STAT.PAYLOAD AS PAYLOAD, STAT.REFERENCE AS REFERENCE, STAT.SEQUENCE_NO AS SEQUENCE_NO, STAT.ID_MANDATOR AS ID_MANDATOR, STAT.ALERTED AS ALERTED, STAT.STATUS AS STATUS FROM ( SELECT DISTINCT FSI.ID, FSI.ITEM_TYPE, FSI1.DATETIME_VALUE, FSI2.TEXT_VALUE, FSI.COMPANY, FSI.CREATION_TIME, FSI.PAYLOAD, FSI.REFERENCE, FSI.SEQUENCE_NO, FSI.ID_MANDATOR, FSI.ALERTED, FSI.STATUS FROM FRA_STAT_ITEM FSI CROSS JOIN FRA_STAT_FIELD FSI1 CROSS JOIN FRA_STAT_FIELD FSI2 WHERE FSI1.ID_STAT_ITEM=FSI.ID AND FSI1.ID_FIELD_DEF IN (SELECT ID FROM FRA_ITEM_FIELD_DEF WHERE MAPPING='executionDate') AND FSI2.ID_STAT_ITEM=FSI.ID AND FSI2.ID_FIELD_DEF IN (SELECT ID FROM FRA_ITEM_FIELD_DEF WHERE MAPPING='debitAccount') UNION SELECT DISTINCT FSI.ID, FSI.ITEM_TYPE, FSI1.DATETIME_VALUE, FSI3.TEXT_VALUE, FSI.COMPANY, FSI.CREATION_TIME, FSI.PAYLOAD, FSI.REFERENCE, FSI.SEQUENCE_NO, FSI.ID_MANDATOR, FSI.ALERTED, FSI.STATUS FROM FRA_STAT_ITEM FSI CROSS JOIN FRA_STAT_FIELD FSI1 CROSS JOIN FRA_STAT_FIELD FSI3 WHERE FSI1.ID_STAT_ITEM=FSI.ID AND FSI1.ID_FIELD_DEF IN (SELECT ID FROM FRA_ITEM_FIELD_DEF WHERE MAPPING='executionDate') AND FSI3.ID_STAT_ITEM=FSI.ID AND FSI3.ID_FIELD_DEF IN (SELECT ID FROM FRA_ITEM_FIELD_DEF WHERE MAPPING='creditAccount') ) STAT GO Query to fetch and display record in GUI: WITH query AS (select ROW_NUMBER() OVER (order by fraudstats0_.id desc) as __hibernate_row_nr__, fraudstats0_.id as id64_, fraudstats0_.version as version64_, fraudstats0_.alert_type as alert3_64_, fraudstats0_.benef_cust_id as benef4_64_, fraudstats0_.blocked as blocked64_, fraudstats0_.company as company64_, fraudstats0_.creation_time as creation7_64_, fraudstats0_.id_mandator as id8_64_, fraudstats0_.ordering_cust_id as ordering9_64_, fraudstats0_.id_original as id10_64_, fraudstats0_.reference as reference64_, fraudstats0_.sequence_no as sequence12_64_, fraudstats0_.item_type as item13_64_, fraudstats0_.alerted as alerted64_, fraudstats0_.last_evaluation as last15_64_, fraudstats0_.last_eval_time as last16_64_, fraudstats0_.last_eval_user as last17_64_, fraudstats0_.id_live_item as id18_64_, fraudstats0_.payload as payload64_, fraudstats0_.status as status64_ from fra_stat_item fraudstats0_ where fraudstats0_.id in (select fraudstats1_.id from fraud_alert_stat_hit_view fraudstats1_ where fraudstats1_.fraud_item_type='profile.batch.trn.alert' and fraudstats1_.value_date>=@P0 and (fraudstats1_.account in (@P1 , @P2 , @P3) or fraudstats1_.account in (@P4 , @P5 , @P6))) ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN @P7 AND @P8 At present, Archiving/Purging the table is not possible by structure. Could anyone share some suggestion on fine tuning the view?
SSIS : Final Rowcount different than "Row per batch" and "Max Insert Commit Size"
Hi, I have to transfer an hundred millions of rows from one SQL instance to another one (both are SQL 2016 SP2), so I tried to tune at minimum the Dataflow by defining both the Target "Row per batch" and "Max Insert Commit Size" to 10000. My understanding was that these parameters have the same behavior that BatchSize for a Bulk Insert statement, so I expected to find the 10000 value in the rowcounts columns from my SQL Profiler. I can't understand why I'm retrieving the value 3889 instead of the expected 10000 when I run my SSIS package (ref. attached Screenshot). Any idea ? Thanks for your help.