Performance Based Transact Coding

Aug 28, 2006

Hi all,

I am looking for some study meaterial focussed on performance based transact SQL development. I am a fairly well seasoned (3+ years self taught), and I am getting into situations where different query constructs yield the same results, but the performance (execution time and disk I/O) varies.

I have found that sometimes nested select statements execute faster than joins, but usually the opposite is true, for example, and I would like to learn why.

Info would be appreciated.

View 4 Replies


ADVERTISEMENT

Transact SQL :: Tables And Database Not Detecting Auto While Coding

Aug 27, 2015

When I type Select statemet like below, Tables names are not populate auto. I need to type full lenght instead of selection in list.

Ex: I have few tables and database in my schema and need to select while coding.

select * from (Table Name) When i type first char in the table space it will not populate list of tables in my sql studio. 

View 12 Replies View Related

Performance Of An Oracle-based Application

Nov 22, 2007

We have Oracle-based enterprise resource planning (ERP) application that facilitates order entry, invoicing, dispatch, stock management, and purchasing. However, it took as much as 30 seconds to perform queries from the remote offices, which were linked via Frame Relay.

Any experiences or ideas shared will be greatly appreciated.



Best,

Steve

View 5 Replies View Related

Query Performance Problems With Join On UDF-based Computed Column

Aug 25, 2007

We have a table with a couple of computed columns. The value of the computed column represents a foreign key reference into another table. We're seeing a major performance problem doing a query joining between the two tables with one of the columns, but not the other. In other words, this kind of query is very fast:

select * from TheTable A, FKeyTable B
where A.ComputedColumn1 = B.KeyColumn

but this one sends the CPU usage of SQL Server to 99% for a very long time:

select * from TheTable A, FKeyTable B
where A.ComputedColumn2 = B.KeyColumn

The main difference we can see that the computed column that causes problems is based on a UDF, and the other one isn't (but again, both are computed). When I look at the execution plan, the slow query shows a Nested Loop (Inner Join) with a "No Join Predicate" warning, with the estimated # of rows being 70 million (which correponds to the product of 1016 rows in TheTable and 69K rows in FKeyTable). The fast query doesn't have that warning, and shows 1016 rows (the # of rows in TheTable).

Does anyone know why the usage of a UDF would induce this horribly inefficient join behavior? Anything we can do to fix it?

This is SQL Server 2005 SP2, btw.

View 15 Replies View Related

Transact SQL :: How To Get Top 5 Based On A Category

Jun 4, 2015

I have this following query,

select [Parent Name],[ID],[Year],[Sales Name],
sum([Total VtM]) as 'Total Sales'
from RegData
group by [Parent Name],[ID],[Year],[Sales Name]
order by [Total Sales] desc

I need to modify this query to get the top 5 of each category based on Total Sales amount.

View 13 Replies View Related

Transact SQL :: How To Customize Where Statement Based On UDT

May 20, 2015

Display customized data based on customized where statement from UDT.

The UDT is a parameter inside of a stored procedure.

Problem:

A parameter from a stored procedure is
@communication communications readonly

This parameter is a User-Defined Table Types (UDT) It contains criteria based on end-user's selection from a filtration functionality from a webpage.

Four example of filtration critera based on four end-users' selection that is located inside of a table below.

Each UDT table contains different criteria:

Number Criteria
------ --------
1 Phone
3 Email

Number Criteria
------ --------
1 Phone
2 Cellphone
3 email

Number Criteria
------ --------
4 None

Number Criteria
------ --------
2 Cellphone
1 Phone

Is it somehow possible to use the criteria's value as a column name in the where statement? I want to filtrate the data of the table datatable based on id, name and the UDT's criteria.

I was enable to apply the criteria inside of a variable by looping the UDT's table but the next thing is to paste it in the where statement after "id=1 and name" below

select *
from datatable
where id = 1 and name = 'Cost'

How should I do it?

[URL] .....

create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);

[Code] .....

View 4 Replies View Related

Transact SQL :: Updating Only One Row Based On Where Condition

Sep 16, 2008

How can I update only one row doing an update query?
 
For example:
 
update from table1
set category = 'C'
where country = 'Brazil'
 
One want to update only one row with country = 'brazil'

View 5 Replies View Related

Transact SQL :: How To Add SUM Column Based On Each Row Values

Nov 2, 2015

I have the following result query :

Which is produce by following SP :

SELECT
P.StoreID,
P.InventoryDate,
P.ProductID,
SUM(PIT.Quantity * P.ItemUnitWeight) AS TotalWeight,
SUM(PIT.UsedQuantity * P.ItemUnitWeight) AS UsedWeight,

[code]....

What is the way to add a calculated columned Named "Stocked" which will represent the sum of "Remaining" column for each rows ?

View 9 Replies View Related

Transact SQL :: Grouping Based Of Column Value

Nov 5, 2015

Below is the data I have in table name

TeamStatus
T 1 Complete or Escalate
T 2 Pick Up
T 2 Resolve Case
T 1 Pick Up
T 1 Complete or Escalate
T 1 Pick Up
T 1 Complete or Escalate

I want to get he group based of Resolve Case value in Status Column. Anything before Resolve case will be considered as Group 1 and after Resolve Case status should be considered as Group 2. Below is desired new Group column,

Group TeamStatus
Group 1 T 1Complete or Escalate
T 2 Pick Up
T 2 Resolve Case

Group 2 T 1Pick Up
T 1Complete or Escalate
T 1 Pick Up
T 1 Complete or Escalate

View 7 Replies View Related

Transact SQL :: Partitioning Performance With 15 Min Partitions

Oct 27, 2015

We are currently developing an OLTP application, which will need to purge data when it becomes older than 1 hour.Rather than having a process which deletes rows periodically (and risks locking the tables), I am considering using partitioning on a rolling 15 minute window.The idea is to have 5 active partitions, with the 5<sup>th</sup> one being swapped out, merged and a new one split in. This will allow data to live to a max of 1 hour 15 mins,which is acceptable.

Actually, I will have 8 partitions; there will be 4 partitions set in the future, just to ensure when the last partition is split, there isn’t any data movement, as the newest partition will be empty.I am wondering if there will be any performance issues due to partition swapping, merging and splitting every 15 minutes? The application will have a high volume of users when live. I think this should be a better option that continually deleting from the tables.

View 6 Replies View Related

Transact SQL :: Performance Tuning On Triggers

Oct 12, 2015

I have table A and B. A has column ID,A1,A2,A3,A4,A5 columns. B has ID,B1,B2, A1 columns. A table has a trigger. I defined the below trigger. 

Solution 1
ALTER TRIGGER [dbo].[Tri_A]
ON [dbo].[A]
for UPDATE
AS
BEGIN
   UPDATE B
  SET  B.A1= i.A1
  FROM  inserted i
     INNER JOIN B 
      ON B.ID = i.ID
END;
GO

If I change the above solution 1 trigger to solution 2.Can I improve the trigger performance dramatically? I mean only A.A1 is changed then update B.A1. So when the other columns is changed, the update will not be required.

Solution 2
ALTER TRIGGER [dbo].[Tri_A]
ON [dbo].[A]
for UPDATE
AS
BEGIN
IF ( UPDATE (A1) )
   UPDATE B
  SET  B.A1= i.A1
  FROM  inserted i
     INNER JOIN B 
      ON B.ID = i.ID
END;
GO

View 2 Replies View Related

Transact SQL :: How To Count And Sum A Date Based On Conditions

Jun 18, 2015

I have conducted a thorough search in the forums and cannot quite find my answer. I have a date field called open_date. If the open_date is more than 30 days old, I need to count it. I have started with the following code:

SELECT 'Older_Than_30Days' =
CASE
WHEN open.date >= 30 THEN '1'
ELSE '0"
END

My problem is the WHEN.

View 6 Replies View Related

Transact SQL :: Get Records Based On Condition In Server

Nov 22, 2015

I have a question about SQL Server.

Table patient:

create table patient (pn int,code int,date date,doctorcode int)
insert into patient (pn,code,date,doctorcode)
values
(1,10,'2015-02-19','100),
(1,10,'2015-02-19','101),
(1,10,'2015-02-19','102),

[Code] ...

Table Patientref:

create table patientref
(pn int,code int, sdate date,edate date,status int)
insert into patientref(pn,code,sdate,edate,status)
values
(1,10,'2015-02-13','2015-02-19',1),
(1,10,'2015-02-19','2015-03-24',2),

[Code] ...

Here we need consider patient dates that fall between sdate and edate of the patientrefs table, and then we need to consider the highest status values in order (for example, the highest values in order - 2 is first highest, 4 is second highest, 3 is third highest, and 1 is fourth highest value)

If the date falls between multiple different sdate and edate with the same status values, then we need to consider the latest sdate value and from that entire record we need to extract that value.

Examples: patient

pn |  code  |  date      |   doctorcode
2  |  10    |2015-02-12  |   101
2  |  10    |2015-02-13  |   102
2  |  10    |2015-02-14  |   103

Table : Patientref:

pn |  code  |  sdate      |   edate      | Status
2  |  10    |2015-02-08   |   2015-02-19 |  4
2  |  10    |2015-02-09   |   2015-02-19 |  2
2  |  10    |2015-02-10   |   2015-02-19 |  2
2  |  10    |2015-02-11   |   2015-02-18 |  1

Here, pn=2 values have dates which fall between sdate and edate of patientref table. Then we give highest values status is 2, and status 2 values have two records, then we go for max sdate(latest sdate). Then this pn=2 latest sdates is 2015-02-10 and we need to retrieve the corresponding edate and status values.

pn = 4donot have sdate and edate and status values dut not fall conditon 

Based on this, the desired output is below:

pn |  code  |  date      |   doctorcode | sdate     |edate      |status
1  |  10    |2015-02-19  |   100        |2015-02-19 |2015-03-24 | 2
1  |  10    |2015-02-19  |   101        |2015-02-19 |2015-03-24 | 2
1  |  10    |2015-02-19  |   102        |2015-02-19 |2015-03-24 | 2
2  |  10    |2015-02-12  |   101        |2015-02-10 |2015-02-19 | 2

[Code] ...

I tried it like this:

select p.pn,p.code,p.[date],p.doctorcode,pr.sdate,pr.edate,pr.[status] from patient p
 outer apply (select top 1 pr.pn,pr.code,pr.sdate,pr.edate,pr.[status] from patientref pr 
where pr.pn=p.pn and pr.code=p.code and p.date between pr.sdate and pr.edate
 order by case when pr.status=2 
then 1 when pr.status=4 then 2
 when pr.status=3 then 3 
when pr.status=1 then 4 end ,pr.sdate 
 )pr

but this query not given expected result.here when dos not fall between sdate and edate  that records not given in the above query. I required that records also.if not fall b/w condition then we need retrive that records empty values for that records.

View 7 Replies View Related

Transact SQL :: Returning A Response From XML-based Web Service

Aug 18, 2015

I'm running into an interesting issue when returning a response from an XML-based web service. The following code returns good values from PRINT

@response
DECLARE @address varchar(50) = '90210'
DECLARE @URL varchar(MAX)
SET @URL = 'https://somewebsite/map.xml?zip=' +
CASE WHEN @Address IS NOT NULL THEN @Address ELSE '' END
SET @URL = REPLACE(@URL, ' ', '+')

[code]...

However, on some calls to the web service, the value returned is longer than 4000 characters. In these instances, it appears as though it breaks the whole thing and returns nothing. However, if I change the @Response parameter to nvarchar(max), it never returns anything, even on responses that are shorter than 4000 characters. what the fundamental difference is here between nvarchar(4000) and nvarchar(max) with respects to how it stores responseText. More importantly, how to get this to return a value even when it exceeds 4,000 characters?

View 2 Replies View Related

Transact SQL :: Parse Name Field Based On A Delimiter

Sep 18, 2015

how to separate names but i cannot make work in this case.  The name field might contain anywhere from only one name with no delimeters to five names with four delimeters.  I want to replace the delimeter with a space and reorder the names.

Original data format: Name2/Name1/Name3/Name4/Name5.
Desired data format: Name1 Name2 Name3 Name4 Name5.
Examples of source data

Company ABCDoe/JohnSmith/Jim/EtalJones/Jeff/Jr/& Sally
Bush/Jim/Sr/Etal/Trustee

View 43 Replies View Related

Renaming Columns Based On Data Using Transact Sql

Jan 18, 2008

A happy belated New Year to all!

Can someone please help me with the following. How do I, in SS 2000, use a value to label a column?

example

Col A Col B Col C Col D
CA '12/1/07' '11/1/07' '10/1/07'

I want to use the month and year of the date to rename the column.

Col A 12-2007 11-2007 10-2007
CA '12/1/07' '11/1/07' '10/1/07'




Thank you in advance for any help you can offer.


TMendez

View 5 Replies View Related

Transact SQL :: Update Table Based On Join

Sep 7, 2015

I have table A with colums ID and Product. I have table B with ID (same as table A) and revenue of product.

I need to update the field Product of table A with the revenue of table B.

I'm confuse with the update joining both tables. I tried this, but obviously has an error:

Update A set Product=B.Revenue where A.ID=B.ID

View 6 Replies View Related

Transact SQL :: Based On One Column Need To Cascade Rows One By One

Sep 23, 2015

Here's the my structure and data as follows

>
SELECT tranno ,mrno medrecno ,createdon,createdat,no_of_trans nooftrans 
FROM mytab WHERE mrno = 'MR1514' and tranno = 1111
ORDER BY no_of_trans
tranno medrecno
createdon createdat
nooftrans

[Code] ...

Now requirement is : -

tranno medrecno
createdon createdat
nooftrans     tranno medrecno
createdon createdat
nooftrans

[Code] ....

View 4 Replies View Related

Transact SQL :: How To Repeat Rows Based On Column Value

Apr 30, 2015

I have a query that gives me a result with a column value for example 4.

I now want to repeat this row 4 times with a new column that calculated from 1 - 4.

Or when column value is 3 I want to repeat row 3 times with new column name 1-3

View 7 Replies View Related

Transact SQL :: Update Based On Results From Select

Jul 24, 2015

Update statement based on the results from this SELECT:

SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,
RELSP_STATUS_CD
FROM RELEASE_QUEUE WITH (NOLOCK)

[Code] ....

I need to update all records where the 

RELEASE_STEPS.RELSP_STATUS_CD = 'SKPD'TORELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'

I imagine that the UPDATE statement will be something like:

UPDATE RELEASE_STEPS SET dbo.RELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'
WHERE (
SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,

[Code] .....

View 4 Replies View Related

Transact SQL :: How To Frame A Range Based On Amount

Oct 24, 2015

I want to frame a range of data based on particular group of columns

If OBJECT_ID('tempdb..#ResellerRange') IS NOT NULL
    drop table #ResellerRange
create table #ResellerRange
(   ResID        varchar(10)
   , amt    decimal(18,2)
   , serialno    int)

   insert into #ResellerRange ( ResID,amt, serialno )
   values ('Raja',10,67),('raja',10,68),('raja',10,89),('Prabu',20,56)

I want below output
 
   resid    amt    min    max
    ----------------------------------   
   raja      10    67     68
    raja      10    89     89
    Prabu     20    56     56

View 5 Replies View Related

Transact SQL :: Accumulate Values Based On Time For Each Day

Jun 10, 2015

I have a table Mytable which has two fields (Date, Value) and record is based on every hour like this:

Date Value
1999-01-01 00:00:00 10
1999-01-01 01:00:00 8
1999-01-01 02:00:00 6
...
1999-01-01 23:00:00 12
1999-01-02 00:00:00 9
1999-01-02 01:00:00 5
...
1999-01-02 23:00:00 2

How can I use a query to get accumulate value for the day? the result should look like this:

Date Value Accumulated_Value
1999-01-01 00:00:00 10 10
1999-01-01 01:00:00 8 18
1999-01-01 02:00:00 6  24...
1999-01-01 23:00:00 12 36
1999-01-02 00:00:00 9 9
1999-01-02 01:00:00 5 14...
1999-01-02 23:00:00 2 16

The accumulated value should be based on every hour for the day and then repeat for the second day and so on.

What is the best way to do this? I am using SQL 2008

View 2 Replies View Related

Transact SQL :: How To Get Four Quarter End Dates Based On Given Date

Jun 3, 2015

I have a simple following table which is having only one date column.

CREATE TABLE TEST_DATE
(
InputDate DATE
)
GO
INSERT INTO TEST_DATE VALUES('01-01-2015')
INSERT INTO TEST_DATE VALUES('06-25-2015')
INSERT INTO TEST_DATE VALUES('11-23-2014')
GO
SELECT * FROM TEST_DATE;

And the expected out put would be as follows:

I want to derive a Four Quarter End Date based on Date selected.

For Example if i select 01-01-2015 then
First Quarter End Date would be Previous Quarter End Date
Second Quarter End Date would be Current Quarter End Date
Third Quarter End Date would be Next Quarter End Date
Fourth Quarter End Date would be Next +1 Quarter End Date Like that

View 9 Replies View Related

Transact SQL :: How To Get Results Based On Conditional Where Clause

Jul 14, 2015

My source table has two columns... Policynum and PolicyStartdate and data looks like..
.
Policynum              PolicyStartdate
123G                       01/01/2012    
456D                       02/16/2012     
789A                       01/21/2012
163J                       05/25/2012

Now my output should return based on 3 parameters..

First two parameters are date range... let say @fromdt and @todt

Third parameter is @policynum

Scenario-1: Enter dates in date range param and leave policynum param blank
Ex: policystartdate between '01/01/2012 and '01/31/2012'.... It returns 1st and 3rd rows from above in the output

Scenario-2: enter policy num in policynum param and don't select any dates
Ex:  policynum ='456D'     It returns 2nd row in the output

Scenario-3: Select dates in date range param and enter policynum in param
Ex: policystartdate between '01/01/2012 and '01/31/2012' and policynum
='163J'.  it should return only 4th row even though dates were selected(Override date range when policynum is entered in param and just return specified policynum row in the output)

I need t-sql code to get above results.

View 12 Replies View Related

Transact SQL :: Calculate Average Based On A Scenario

Oct 30, 2015

Col1                      Col2       Col3          Col4
54763.00              21           0              0             
--Row1
59574.00              23           0              0             
--Row2
64085.00              20           0              0             
--Row3
0.0               0.00      0           0              0             
--Row4

I'm trying to calculate Average of Col1 of above table based on below scenario:

CASE WHEN all the columns in the above table are “0” (as highlighted) THEN I want AVERAGE of Col1 as (Row1+Row2+Row3)/3

ELSE if at least one of the column of highlighted row has value other than “0”, THEN I want the AVERAGE of Col1 to be (Row1+Row2+Row3+Row4)/4

View 7 Replies View Related

Transact SQL :: Deleting Records Based On Certain Logic

May 7, 2015

There are two seperate jobs,Job A and Job B, which run and insert records in a table. Job A runs first and then Job B runs. The task is to overwrite Job B records if Job A and Job B have same

PID,EDate,CP,RelativePNum,XrefCode,CPD 
PID,EDate,CP,RelativePNum,XrefCode,CPD 

In the above screenshot, row 1 and row 2 have same

So i want to write a query which deletes row 2 records, and keep row 1,row 3 and row 4 records. This i want to do in the whole table.

create
table AB
(
PID
int,
tTimeStamp
datetime,
EDate 
varchar(40),

[Code] ....

View 12 Replies View Related

Transact SQL :: Calculate Data Based On Position

Jun 4, 2015

I have a table "PrintArea" with below table structure.

The Details column have " " separated data.

CREATE TABLE [dbo].[PrintArea](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AccessDate] [datetime] NULL,
[IPAddress] [varchar](16) NULL,
[SerialNum] [varchar](256) NULL,
[Description] [varchar](256) NULL,
[Details] [varchar](2048) NULL,
)

This is how the data lookis like.

INSERT [dbo].[PrintArea] ([ID], [AccessDate], [IPAddress], [SerialNum], [Description], [Details]) VALUES (104280, CAST(0x0000A28A00A88310 AS DateTime), N'70.89.39.161', N'InSign-f529c824-85a6-4a64-8c59-e9f786d718f3-YM2AS31367,BFEBFBFF000306A9', N'Printing', N'Headboard_Insert_4
1
1
7938
5579

[Code] ...

I want to calculate totalAreaPrinted based on below formula from Details column.

View 2 Replies View Related

Transact SQL :: Where Clause Based On Select Statement From Another Table

Jul 9, 2015

I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:

SELECT tblGRNItem.GRNID
FROM tblGRNItem
INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp
WHERE tblGRNItem.DateCreated BETWEEN
(SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT

[code]....

View 6 Replies View Related

Transact SQL :: How To Reset Running Total To 0 Based On Other Column Value

Nov 9, 2015

I am trying to calculate the the running total but also tried to reset to reset to zero based on a value of a column.

here I am trying to calculate the value of CalcVal column based on column Flag value...actually it is running total but it reset to zero if Flag value is 0.

Here is the example of data along with required column

MonthId Flag CalcVal 

201401 1 1
201402 1 2
201403 1 3
201404 1 4
201405 1 5

and so on .........

View 8 Replies View Related

Transact SQL :: Merge Two Rows Into One Based On Same Customer Number

Oct 26, 2015

I have a table Customer with below column

CustomerNumber , FName,LName,DOB

I have either 2 or 1 row for each customer number  I want to write a SQL to pull data into 1 table based on same CustomerNumber, if only one row is present for the particular customer number it should pull null in FName2,LName2,DOB2 columns.

example:
CustNumber  Fanme   Lname   DOB
1  Sam tiller 08/26/1981
1  Joe   timy  01/02/1986
2  jack  niks   09/09/1990

Result I want:
CustNumber Fname1  Lname1 DOB1 Fname2 Lname2 DOB2
1  Sam  Tiller 08/26/1981 Joe  timy 01/02/1986
2  Jack  niks  09/09/1990 null null null

View 7 Replies View Related

Transact SQL :: Update Column Based On Row Number For Previous Row

Jul 25, 2015

Below is the resultset I got using the following SQL statement

SELECT   ROW_NUMBER() OVER (PARTITION BY ID ORDER BY create_date DESC) AS RowNum
,ID
,create_date
,NULL AS end_date
FROM dbo.Table_1

Resultset:

RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 NULL

[Code] ....

Now, I want to update the end_date column with the create_date's values for the next row_number. Desired output is shown below:

RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 2015-02-18

[Code] ....

View 4 Replies View Related

Transact SQL :: Add Position Of Record Based On Certain Date Of Transaction

Aug 4, 2015

I built the following query to add the position of a record based on a certain date of the transaction of a customer:

select
CustomerID
, Product
, purchasedate

[code]....

View 6 Replies View Related

Transact SQL :: Break Down Content Based Of Hourly Basis

Oct 12, 2015

I am trying to break down the content based of hourly basis. It works fine when there are values for that specific hour but if there are entries or values for a specific hour then it returns null instead of 0. How not to get null instead get zero.

Here is the code below: 

With temp_exp As
(Select pl.state,Cast(signeddate As date) As signatureDate, signeddate As DoneTime From contract c with(nolock) where c.signeddate>DateAdd(Day, Datediff(Day,0, GetDate()), 0)
)
Select
Sum(Case When CONVERT(varchar(8),DoneTime,108) Between '07:00:00' And '07:59:59' Then 1 Else 0 End) '8AM',

[Code] ....

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved