自外部Join替代方案，用于从Temp表插入Physical表 [英] Self outer Join alternative for inserting into Physical table from Temp table

问题描述

如何提高此查询的性能？

How to improve the performance of this query?

INSERT INTO ABC(TRACKING_ID,GROUP_ID,ETL_NUM,ENTITY_ID,UNI_ID,DOS_TO)
SELECT A.TID,A.TID2,A.ETL_NUM,A.ENTITY_ID,A.UNI_ID,A.DOS_TO
FROM #TEMP A(NOLOCK)
LEFT OUTER JOIN #TEMP B(NOLOCK) ON A.TID=B.TID
AND ETL_NUM<B.ETL_NUM
WHERE B.TID IS NULL

我有1100万条记录。处理此查询需要9个小时。为了优化此查询，我在临时表ETL_NUM上添加了索引。但是它并没有提高性能。

I have 11 million records. It takes 9 hours to process this query. For tuning this query i have added index on Temp table ETL_NUM. But it didnt improve performance.

我没有数据库管理员权限。

I don't have Admin privileges on DB.

推荐答案

是否要向没有管理员权限的数据库中插入1100万条记录？

Do you want to insert 11millions records into the DB you don't have Admin authority?

您要手动插入所有数据吗？因此，您认为大约需要9个小时。还是只写下一个查询就需要9个小时？

And you want to insert all the data manually? So, you think it takes around 9 hours. Or for just one query you wrote down takes 9hours?

好吧，如果您谈论的是前一个，那取决于您如何处理数据。如果您使用JSON或样式表或此类文件中的数据。可能会更容易。如果是后者，则可能需要询问管理员并进行讨论。

Well, If you are talking about the former one, It depends how you have the data. If you have the data in JSON or in a stylesheet or this kind of file. It can be easier. If it is latter one, you may need to ask the admin and discuss about it.

这篇关于自外部Join替代方案，用于从Temp表插入Physical表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！