November 25, 2009 at 1:44 am
Hi all,
I wrote a very complexe SELECT statement and it is running 3 seconds with a result of 6 records. If I expand the statement to INSERT INTO ..... SELECT * FROM it is running 22 seconds.
What is the explaniation of this performance gap?
I tried different kinf of tables, physical tables on different partitions, temporary tables with # and @ and I tried SELECT * INTO ... but the result was always the same.
I am working with SQL-Server 2005 Enterprice Edition (64 Bit) on Microsoft Windows NT 5.2 (3790) with 8 AMD processors and 12286 (MB) of memory.
Many thanks in advance.
Regards,
Thomas
SELECT
RTRIM(SUBSTRING(data,18,13)) AS Competitor_Name,
SUBSTRING(data,14,3) AS OPIS_CITY_NO ,
CASE LOWER(RTRIM(SUBSTRING(data,32,1))) WHEN ''
THEN 'b'
ELSE LOWER(RTRIM(SUBSTRING(data,32,1)))
END AS branded_code,
SUBSTRING(data,1,1) AS product_indicator,
RTRIM(SUBSTRING(data,3,1)) AS product_type,
SUBSTRING(data,58,3) AS opis_product_grade,
CASE RTRIM(SUBSTRING(data,54,3)) WHEN ''
THEN '000'
ELSE RTRIM(SUBSTRING(data,54,3))
END AS octane_no,
CASE RTRIM(SUBSTRING(data,76,3)) WHEN ''
THEN '000'
ELSE RTRIM(SUBSTRING(data,76,3))
END AS RVP_GROUP_CODE,
ROUND(CASE WHEN IsNumeric (RTRIM(SUBSTRING(data,40,6))) = 1
THEN
CASE
WHEN SUBSTRING(data,34,1) IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
THEN CAST (RTRIM(SUBSTRING(data,40,6)) AS DECIMAL(17,5)) / 100.0
* (1.0 - (CAST (SUBSTRING(data,34,1) as INT) / 100.0))
ELSE CAST (RTRIM(SUBSTRING(data,40,6)) AS DECIMAL(17,5)) / 100.0
END
ELSE 0.0 -- To be ignored
END, 4) AS price,
CASE WHEN IsNumeric (RTRIM(SUBSTRING(data,40,6))) = 1
THEN CAST (RTRIM(SUBSTRING(data,40,6)) AS DECIMAL(17,5)) / 100.0
ELSE 0.0
END AS GROSS_price,
CASE WHEN IsNumeric(SUBSTRING(data,47,6)) = 1
THEN CAST (REPLACE(SUBSTRING(data,47,6),' ','') AS DECIMAL(17,5)) / 100.0
ELSE 0.0
END AS price_change_amt,
SUBSTRING(data,62,8) AS i_prc_date,
RTRIM(SUBSTRING(data,34,4)) AS i_prc_local_char_col1,
CASE SUBSTRING(data,71,1) WHEN 'o'
THEN 1
ELSE 0
END i_prc_local_num_col2,
i_prc_cps_code AS cps_code,
i_prc_pro_code AS pro_code
FROM OPISData801
LEFT OUTER JOIN V_US_MW_OPIS_CPSMAP CPS ON
COMPETITOR_NAME = RTRIM(SUBSTRING(DATA,18,13)) AND
CPS.OPIS_CITY_NO = SUBSTRING(DATA,14,3) AND
CPS.BRANDED_CODE = CASE LOWER(RTRIM(SUBSTRING(DATA,32,1))) WHEN ''
THEN 'B'
ELSE LOWER(RTRIM(SUBSTRING(DATA,32,1)))
END
LEFT OUTER JOIN (
SELECT DISTINCT
LEN(pop_grade) AS pop_grade_length,
pop_indicatorAS product_indicator,
pop_typeAS product_type,
pop_gradeAS opis_product_grade,
RIGHT(REPLICATE('0',3) + CONVERT(VARCHAR(3),pop_octane_no),3) AS octane_no,
CASE pop_rvp_code
WHEN ''
THEN '000'
ELSE pop_rvp_code
END AS RVP_GROUP_CODE,
cps_local_code AS OPIS_CITY_NO,
LOWER(pop_branded_code) AS branded_code,
pop_company_code AS i_prc_pro_code,
1 AS ipm_active
FROM
product_opis
INNER JOIN product
ON ISNULL(pro_company_code,'') = pop_company_code
INNER JOIN lnk_sit_pro
ON pro_id = lsp_pro_id
INNER JOIN site
ON lsp_sit_id = sit_id
INNER JOIN lnk_sit_cps
ON lsc_sit_id = sit_id
INNER JOIN compsite
ON cps_Id = lsc_cps_id AND
cps_zip = 'OPIS'
WHERE
LEN(ISNULL(cps_local_code,'')) > 0 AND
LEN(pop_grade) > 0
UNION
SELECT DISTINCT
LEN(pop_grade) AS pop_grade_length,
pop_indicatorAS product_indicator,
pop_typeAS product_type,
pop_gradeAS opis_product_grade,
RIGHT(REPLICATE('0',3) +
CONVERT(VARCHAR(3),pop_octane_no),3) AS octane_no,
CASE pop_rvp_code
WHEN ''
THEN '000'
ELSE pop_rvp_code
END AS RVP_GROUP_CODE,
ISNULL(cps_local_code,'') AS OPIS_CITY_NO,
LOWER(pop_branded_code) AS branded_code,
pop_company_code AS i_prc_pro_code,
1 AS ipm_active
FROM
product_opis pop
INNER JOIN product
ON ISNULL(pro_company_code,'') = pop_company_code
INNER JOIN lnk_cps_pro
ON pro_id = lcp_pro_id
INNER JOIN compsite cps
ON lcp_cps_id = cps_id AND
cps_zip = 'OPIS'
WHERE
LEN(ISNULL(cps_local_code,'')) > 0 AND
LEN(pop_grade) > 0
UNION
SELECT DISTINCT
LEN(poi_grade) AS poi_grade_length,
poi_indicatorAS product_indicator,
poi_typeAS product_type,
poi_gradeAS opis_product_grade,
RIGHT(REPLICATE('0',3) + CONVERT(VARCHAR(3),poi_octane_no),3) AS octane_no,
CASE poi_rvp_code WHEN '' THEN '000' ELSE poi_rvp_code END AS RVP_GROUP_CODE,
RIGHT(REPLICATE('0',3) + poi_opis_city, 3) AS OPIS_CITY_NO,
LOWER(poi_branded_code) AS branded_code,
'IGNORE'AS i_prc_pro_code,
0AS ipm_active
FROM
product_opis_ignore
WHERE
poi_active <> 0
) AS pro ON
pro.product_indicator = SUBSTRING(data,1,1) AND
pro.product_type = RTRIM(SUBSTRING(data,3,1)) AND
pro.opis_product_grade = SUBSTRING(data,58,3) AND
pro.octane_no = CASE RTRIM(SUBSTRING(data,54,3)) WHEN ''
THEN '000'
ELSE RTRIM(SUBSTRING(data,54,3))
END AND
pro.RVP_GROUP_CODE = CASE RTRIM(SUBSTRING(data,76,3)) WHEN ''
THEN '000'
ELSE RTRIM(SUBSTRING(data,76,3))
END AND
pro.OPIS_CITY_NO = SUBSTRING(data,14,3) AND
pro.branded_code = CASE LOWER(RTRIM(SUBSTRING(data,32,1))) WHEN ''
THEN 'b'
ELSE LOWER(RTRIM(SUBSTRING(data,32,1)))
END AND
pro.ipm_active <> 0 AND
cps.ipm_active <> 0
November 25, 2009 at 6:00 am
I found a solution to reduce the time to 4 seconds by reorganizing the underlying view but this answers not the question why the gap betwenn INSERT and SELECT is hugh.
Regards,
Thomas
November 25, 2009 at 7:31 am
Without looking at the efficiency of the query, it's pretty easy to explain why you would see a difference between these two. A SELECT statement just pulls the data. When you're doing an INSERT and a SELECT you're doing the work of the SELECT statement plus you're writing data out to the disk. In addition to the simple write of the data retrieved by the SELECT statement you may also be seeing indexes getting updated. All that is going to combine to make an INSERT with a SELECT more expensive than the SELECT alone. It just follows from the work being done.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 25, 2009 at 8:34 am
Yes, in general I agree but in this special case the insert of 6 records needs about 16 seconds for a table without any index.
The execution plan for the insert statement shows that the insert operation needs 0% of the time.
So why takes the select statement 3 seconds and the combined insert statement 21 seconds?
November 25, 2009 at 10:28 am
Please attach the actual execution plan.
Are you inserting into an updatable view? IS the query time 4, 16, or 21 seconds, or all of the above?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 25, 2009 at 11:08 am
Is it consistently the same time or does it vary. You might be hitting contention on the inserts.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 25, 2009 at 11:48 am
I agree with Grant... there may be some contention in the form of blocking on the table you're inserting into. There may be a couple of other things at play, as well.
1. What logical order are the inserts being accomplished compared to the order of the clustered index on the inserted-to table? If they are different, index fragmentation due to page splits may be occurring and page splits can and will take a long time to resolve.
2. How many indexes do you have on the inserted-to table? The more indexes you have, the longer it will take. Indexes also suffer from "page splits" except they can actually be worse. Where a clustered index will split of one extra page, and index will split off a whole extent (8 pages). You can certainly decrease both problems by doing regular index maintenance, making sure that you actually do have a clustered index, and that the FILL FACTOR is set appropriately to support inserts. Of course, that turns out to be a bit of a balancing act because although lower FILL FACTORs may be great for "out of order" inserts, they don't lend themselves to performance on SELECTs very well because a SELECT may have to read over a great amount of "empty" space on each page.
3. Are there any triggers on the inserted-to table? Those can be a huge drain if they're not written just right. Even when optimized, they do make for extra code that needs to be executed and, if they read or write to other tables, may form additional sources of blocking contention because other tables are involved.
4. Are there any indexed views on the table? Indexed views are basically materialized in the background and they must be updated just as if (theoretically speaking) there were a trigger on the table that inserted/updated another table.
5. How many foreign key constraints are there on the table? Foreign keys cost extra time in that they must check the foreign tables to see if the data is available.
6. How many other constraints to you have? Except for the NOT NULL flavor of constraints, check constraints can use quite a bit of extra time especially if they use a UDF as part of the check.
7. Are the data types of the inserted data the same as the data types in the insert-to table? If they are not, it will take extra time to do the implicit conversions.
I've probably missed some of the reasons for such a disparity between a SELECT and an INSERT/SELECT, but those are the first things that I'd check for.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2009 at 3:01 am
The table looks like follows:
CREATE TABLE #tmpImport (
Competitor_Name varchar(13),
OPIS_CITY_NO varchar(3),
branded_code varchar(1),
product_indicator varchar(1),
product_type varchar(1),
opis_product_grade varchar(3),
octane_no varchar(3),
RVP_GROUP_CODE varchar(3),
price decimal(17, 5),
GROSS_price decimal(17, 5),
price_change_amt decimal(17, 5),
i_prc_date varchar(8),
i_prc_local_char_col1 varchar(10),
i_prc_local_num_col2 decimal(17, 5),
cps_code varchar(10),
pro_code varchar(20)
)
No indexes, no check constraints.
The SELECT needs 3 seconds and the INSERT/SELECT needs 21 seconds.
November 26, 2009 at 11:25 am
Thomas Lampe (11/26/2009)
The table looks like follows:
I assume that's the one you're inserting into... are the datatypes the same as what the query is producing?
I guess I don't know why what you say is happening is happening. I believe that I'd try a SELECT/INTO to create and populate the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 27, 2009 at 5:39 am
Since it's a temp table, what does your tempdb look like? Are you possibly seeing a lot of resource contention there? Do you have multiple file groups (and drives) assigned to tempdb?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 27, 2009 at 9:18 am
are you really inserting your output into the temp table shown? If so, try these things:
a) insert a few rows using explicit VALUES just to see if they take several seconds each
b) check for blocking when the query is running
c) check for IO stalls on tempdb
d) compare execution plans for the main SELECT between the simple output execution and the INSERT run. could be a vastly different plan.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply