ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,656 through 2,670 (of 10,143 total)

RE: CASE vs ISNULL? Which is faster?

Bhushan Kulkarni (2/1/2015)
Performance of both will be same. I tested both queries on my test database. I got same execution plan for both ISNULL and Case statement
If you look at...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 2, 2015 at 6:30 am

#1774755
RE: Sql Select query is not working on one table but the same is working on copy of the table

praveenkumarpalla (2/2/2015)
Thank you for the reply, Yes I agree with you...
My requirement is like user will upload the excel in which there will be a lab id, block id, mutation1,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 2, 2015 at 3:40 am

#1774723
RE: Sql Select query is not working on one table but the same is working on copy of the table

You've probably got an optimiser timeout on that query. I count ten tables in the FROM list, and that's counting the table-valued function as one table. More than about seven...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

February 2, 2015 at 2:14 am

#1774710
RE: Need Help !! Its urgent

Fantastic, thanks for going to the trouble of posting this up Chris.
Here's IO and timing stats for both queries as-is:
LIKE ========================================
(229 row(s) affected)
Table 'TestData'. Scan count 9, logical reads 10605,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2015 at 7:25 am

#1774343
RE: Need Help !! Its urgent

Christopher Kutsch (1/30/2015)
Try using PATINDEX instead of LIKE. I have had good luck using this method where indexes are available on the search column.
DECLARE @status NVARCHAR(50)
,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2015 at 6:26 am

#1774320
RE: Deleting 10 million rows from table

vsuresh (1/30/2015)
A lot of unwanted rows got entered into a table due to a faulty script. This caused the database to grow by 6GB. I need to delete these rows....
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2015 at 5:14 am

#1774299
RE: Update or Insert SQL Codding help

atul.jadhav (1/30/2015)
Dear expert
I am using import/export wizard to upload excel in sql table
I have primary key in my table
and all primary key value has been change every day
i.e. I have...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2015 at 5:09 am

#1774297
RE: Display one row for the query

Try this:
CREATE TABLE #tbl_TMIS_DATASET (
[BL_ID] [decimal](10, 0) NOT NULL,
[VESSEL] [nvarchar](10) NULL,
[VOYAGE] [nvarchar](12) NULL,
[LEG] [nchar](3) NULL,
)
INSERT INTO #tbl_TMIS_DATASET
VALUES('19516116','HJAMRC','0010E','E')
CREATE TABLE #MG_BL_ITINERARY(
[BL_ID] [numeric](10, 0) NULL,
[LEG_SEQ_NBR] [numeric](3, 0) NULL,
[VESSEL_CD] [varchar](10) NULL,
[VOYAGE_CD] [varchar](12) NULL,
[LEG_CD] [char](1)...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 30, 2015 at 1:58 am

#1774265
RE: Need Help !! Its urgent

There may be a chance here...can you provide a few examples of si.ShippingKey and the range of o.OrderNumbers which you would expect to match? You may be able to...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 29, 2015 at 8:53 am

#1774042
RE: update data based on case condtion

Run this query and post back the results:
SELECT
[EVT.VAL42 = 0] = SUM(CASE WHEN EVT.VAL42 = 0 THEN 1 ELSE 0 END),
[EVT.VAL42 <> 0] = SUM(CASE WHEN EVT.VAL42 <> 0...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 29, 2015 at 7:03 am

#1773980
RE: update data based on case condtion

farrukhhameed786 (1/29/2015)
I am doing like that but if EVT.VAL42 >0 the first update query not run it goes to second update
IF NOT EXISTS (SELECT ...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 29, 2015 at 6:28 am

#1773960
RE: Display one row for the query

pwalter83 (1/29/2015)
Hi,
For the following query, I am trying to fetch only one row (no duplicates) for each BL_ID based on
the logic that if I have multiple rows for one...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 29, 2015 at 5:35 am

#1773946
RE: update data based on case condtion

farrukhhameed786 (1/28/2015)
Hi
How to do this?
Without seeing the code you have already written which would help to describe your problem, it would have to be a guess. Something like
UPDATE MyTable SET...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 28, 2015 at 6:53 am

#1773671
RE: update data based on case condtion

farrukhhameed786 (1/28/2015)
Hi Experts !
I have two sql queries needs to run on condition. In Sub Query if the column having value 0 then run first update statement else...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 28, 2015 at 6:19 am

#1773661
RE: Select Alternate number of rows

Mark Cowne (1/28/2015)
ChrisM@Work (1/28/2015)
GilaMonster (1/28/2015)
Bhushan Kulkarni (1/28/2015)
Optimized one..
USE [AdventureWorks2008]
GO
SELECT P.*
FROM
Person.Person P
WHERE
P.BusinessEntityId & 1 = 0
That doesn't return alternate rows, it returns rows with odd values of...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

January 28, 2015 at 5:03 am

#1773642

Viewing 15 posts - 2,656 through 2,670 (of 10,143 total)