Optimize Query

1

Respected Techie,

Kindly help me to write the below query in much better way. being new to sql, the below query looks very ugly.

Code: how can i combine all three query in much better way.

Query1

SELECT PIC.SKU,PRT.OwnerName,PIC.rollover ,PIC.Competitor_SKU
INTO #MYTEMP2
FROM Product PRT
INNER JOIN
Product_mychange PIC
ON PRT.SKU = PIC.SKU
WHERE OwnerName IN (SELECT [ownertype] FROM DRIVER)
AND PIC.rollover in ('Thales','REP')

select *
into #set1
from #MYTEMP2 ot
where not exists (
select 1
from #MYTEMP2 it
where it.[rollover] = 'REP'
and it.SKU = ot.SKU
)

query2

SELECT * FROM
(
SELECT DISTINCT ISNULL (fd.[Writer],'')[Writer],
S1.SKU,
S1.OwnerName [ownertype],
S1.rollover,
PRT.OwnerName [Needed SKU Type] ,
RANK() over (Partition by SUBSTRING(S1.SKU,1,3) order by fd.Rank) as rank_number
FROM #set1 S1
INNER JOIN
Product PRT
ON S1.SKU = PRT.SKU
inner join
rolalog_DataEntry fd
on fd.[rol Cod] = substring(s1.SKU,1,3)
) R
WHERE R.rank_number = '1'

query3

SELECT * FROM
(
SELECT
CDE.[Writer],
T.SKU ,
T.OwnerName [Base SKU OwnerName],
rollover ,
Competitor_SKU,
PRT.[OwnerName][Rep SKU OwnerName],
RANK() over (Partition by SUBSTRING(T.SKU,1,3) order by cde.Rank) as rank_number

FROM #MYTEMP2 T

INNER JOIN
Product PRT
ON T.Competitor_SKU = PRT.SKU
INNER JOIN
rolalog_DataEntry CDE
ON CDE.[rol Cod] = substring(T.SKU,1,3)

WHERE
(
rollover = 'REP'
and PRT.OwnerName not like
(SELECT [Needed REP SKU Type] FROM DRIVER_Product_Missing_REP_Product)+'%'
)

) S2
WHERE S2.rank_number = '1'

DROP TABLE #MYTEMP2
  DROP TABLE #set1

Kindly share yout expertise

2

Please provide:

  • table descriptions as create statements
  • sample data as insert statements
  • expected output from the sample data you provide
3

Here's how to do what bitsmed is asking: http://www.sqlservercentral.com/articles/Best+Practices/61537/

4

Please follow basic Netiquette AND post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements.

You failed. Do you really have only one product? The rest of the data element names are wrong, etc.

You should follow ISO-8601 rules for displaying temporal data (https://xkcd.com/1179/). We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

SQL is based on a tiered architecture. The database tier handles all of the database retrieval and data integrity. But nothing else. The data display and formatting is done in presentation layers that get data from the database layer.

SQL is declarative, but you are using temp tables the way you used scratch tape files in your old programming language. Even worse, the Sybase SELECT..INTO.. syntax is nothing like ANSI/ISO standard SQL. You are learning speak SQL like a Hillbilly!

We prefer to use CTEs and derived tables that can be optimized instead of wasting disk space and time on temp tables. But the temp tables look just like your old, non_SQL scratch tapes and you do not have to learn a new model of data.

Look at “WHERE owner_name IN (SELECT owner_type FROM Drivers)”

How can a name be a type? These are very different attribute proprieties. Does your “blood_type” have a “blood_name” that means the same thing? Silly, unh? Before you can write good SQL, you have to know data modeling. Each data element has one and only one name.

never use SELECT * in production code except AS part of [NOT] EXISTS( SELECT *..)

I tried to re-write all of this, but that is impossible without DDL and specs. Do you want to try again and follow forum rules, after you do a little research and clean up?

WITH Query1 --- get a real name!
(SELECT PIC.sku, PRT.owner_name, PIC.rollover, PIC.competitor_sku
FROM Products AS PRT,
Product_Mychangeb AS PIC
WHERE PRT.sku = PIC.sku
AND owner_name IN (SELECT owner_name FROM Drivers)
AND PIC.rollover IN ('Thales', 'REP')),

Set_1
AS
( .. ),

query2
AS
(.. ),

query3
AS
( ..),

...