Limit Joins (or Plain Improve Qry)

Jun 3, 2008

Hi,

I got a qry that takes 10s. This is off course too long.
As I described in my earlier posts, I'm no sql hero, but I'm on a neverending quest to improve.
Currently I need a select statement over several (7) tables and return fields from almost all of them.
Furthermore I have optional restrictions on fields spread over all those tables, based on a set of parameters.
What I initially do is inner join all my tables and then have a big where clause that adds where-rules depending on the arguments given.

Like so:
select c.clr_id, n.clr_nm, co.long_nm, p.paint_cd, a.appl_desc, m.modl_nm, y.yr_num
from CLR c
inner join paint_cd p
on c.clr_id = p.clr_id
inner join clr_use_yr y
on y.clr_id = c.clr_id
inner join co
on co.co_id = c.co_id
inner join modl m
on y.modl_id = m.modl_id
inner join clr_appl ca
on c.clr_id = ca.clr_id
inner join appl a
on ca.appl_cd = a.appl_cd
inner join clr_nm n
on c.clr_id = n.clr_id
WHERE
(CASE @regn when '' THEN '' ELSE n.regn_cd END) like (CASE @regn when '' THEN '' ELSE @regn END)
AND (CASE @co when '' THEN '' ELSE co.long_nm END) like (CASE @co when '' THEN '' ELSE @co END)
AND (CASE @clrNm when '' THEN '' ELSE n.clr_nm END) like (CASE @clrNm when '' THEN '' ELSE @clrNm END)
AND (CASE @paintCd when '' THEN '' ELSE p.paint_cd END) like (CASE @paintCd when '' THEN '' ELSE @paintCd END)
AND (CASE @applDc when '' THEN '' ELSE a.appl_desc END) like (CASE @applDc when '' THEN '' ELSE @applDc END)
AND (CASE @useYear when '' THEN '' ELSE cast(y.yr_num as varchar(4)) end) like (CASE @useYear when '' THEN '' else @useYear END)
AND (CASE @modlNm when '' THEN '' ELSE m.modl_Nm END) like (CASE @modlNm when '' THEN '' ELSE @modlNm END)

It appeared to me that by getting those where clauses in the join, I would limit the calculations. So the joins where similar to this:
inner join paint_cd p
on c.clr_id = p.clr_id
AND (CASE @paintCd when '' THEN '' ELSE p.paint_cd END) like (CASE @paintCd when '' THEN '' ELSE @paintCd END)

The result though, was exactly the same.
So, where in my thinking am I wrong and what is the correct way to go about this?

View 9 Replies


ADVERTISEMENT

Generate Db Script In Plain Sql

Apr 26, 2007

Hello. I am really new to sql server. I have a db in the server that I use for testing. i want to export it in plain sql so as to import it in another db system. How can I do it?

thank you a lot.

View 8 Replies View Related

Sp_password In Plain Text

Oct 1, 2007

Greetings all.
I am currently working to improve the security on a legacy application we have at my company. The app was written in vb6 years ago. We now have the app running against a sql 2005 server. One of the function/screens in the application is used to administrate users. (each user has a sql user id) and one of the functions is to reset the password. The vb code uses a call to sp_password. Here is the problem. We setup a network sniffer and found the command being in plain text. While the user logon is encrypted ( SSL Fallback) the sp_password commands issued by the app are plan text. Anyone know of a way to make this encrypted?
Leif

View 3 Replies View Related

Convert RTF To Plain Text

Jul 23, 2005

I have a SQL Server 2000 table with a few fields of "text" data typethat contain rich text. I have to downstream this data and therecipient cannot handle rich text. I need to figure out a way toconvert it back to plain text. Any suggetions?TIA

View 4 Replies View Related

Plain Text Mails

Apr 19, 2007

How can I set the default body_format in database mail. I would like to send a warning with plain text format (from the alerts), that i'll get on my mobile phone, but the message's format is html... always...and I don't get the sms-s.

View 5 Replies View Related

Plain Text Rendering - Again!

Nov 29, 2007

Hello List,



sorry to raise this again here. I have seen a lot of posts on this board about plain text rendering but never really a satisfactory answer. So my question is: Has anyone succeeded in developing a custom rendering extension which is able to render a report as plain text?

I am a Technical Lead in a project to convert and migrate reporting from Unix/C to Reporting Services 2005. One requirement is, to render the reports in plain text for archiving purposes. I know that there are a million options out there to archive in PDF and whatever. Well, we need plain text. No way around it! It is a governmental requirement!

I am about to pull the plug on SSRS 2005 just because we are not able to fulfill this requirement. And we are talking about a big project converting >700 reports!


Challenges we are facing:


Reporting components are rendered as they appear in a compnent list, one after each other. If you now have two multiline components side by side, i.e. two lists, the first list component in the array gets rendered and then the other. This makes it almost impossible to render the lines in those two components side by side. In the sample below, the Report Header would be rendered, then the Address block, then the Other Data block, then the Order History Table and then the Account Statement table.

Positioning of text boxes is almost impossible. We are able to find out how many centimeters/inches/millimeters/points a control is located from the left border. But how can we translate this into i.e. how many spaces will we need to add for padding?

The column width can't be determined. SSRS adds a line break if the cell text is longer than the column width.


We have reports which look something like this:



Code Block


Code Block

REPORT NAME

MANAGER NAME


ACCOUNT
REPORT DATE






First Name: XXXXXXXXXXXX Other Data: XXXXXXXXXXXXXX
Last Name: XXXXXXXXXXXX More Data: XXXXXXXXXXXXXX
Address: XXXXXXXXXXXX More Data: XXXXXXXXXXXXXX
City: XXXXXXXXXXXX

Order History Account Statement

XXXXXXXXXXX XXXXXXX XXXXXX XXXXXXXXXXXXX XXXXXXXX XXXXXXXXXXXXX
XXXXXXXXXXX XXXXXXX XXXXXX XXXXXXXXXXXXX XXXXXXXX XXXXXXXXXXXXX
XXXXXXXXXXX XXXXXXX XXXXXX XXXXXXXXXXXXX XXXXXXXX XXXXXXXXXXXXX
XXXXXXXXXXX XXXXXXX XXXXXX XXXXXXXXXXXXX XXXXXXXX XXXXXXXXXXXXX
XXXXXXXXXXX XXXXXXX XXXXXX XXXXXXXXXXXXX XXXXXXXX XXXXXXXXXXXXX




I also got some advice that SSRS is probably the wrong tool. And I also read this in some of the "Plain Text" posts here. And I am actually surprised about this. People suggest to look for some other tool and not use i.e. email subscrptions, other rendering formats, SSRS security model, snapshot history, report models and all the other goodies only because we can't render in simple text?

If anyone has any information to share, I would very much appreciate it. We also looked at third party tools and plug ins but couldn't find any! If anyone knows of a company who would be able to help us, let me know!

I really would love to pull this project off with SSRS 2005 because I Iike the technology and SSRS provides a lot of functionality we want to leverage. I believe SSRS is the right tool for us and it covers 95% of our business requirements! But at this stage it doesn't look very rosy! Because of the Plain Text rendering!

Cheers

Peter

View 6 Replies View Related

Copy Within A Table - Plain SQL

Aug 9, 2006

Hi.



I'm quit new into SQL - have a problem - and hope someone here ca n help me.

I have a table TABLE1 with different fields. A part of it looks like this:

FIELD1 FIELD2 FIELD3

111111 121212 0

111111 121212 91

222222 232323 0

222222 232323 73

etc. etc.

I want to "copy"/write over '0' in FIELD3 with the number in FIELD3 from the line that has the same numbers (and unique for these two lines) in FIELD1 and FIELD2. The table will then look like this:

FIELD1 FIELD2 FIELD3

111111 121212 91

111111 121212 91

222222 232323 73

222222 232323 73

I can make a temporary table or a new field if necessary. I just don't know how to write the right SQL.

Thankful if anyone can help me.

View 5 Replies View Related

T-SQL (SS2K8) :: CLR / RTF To Plain Text Not Working?

Sep 23, 2014

Trying to troubleshoot an CLR/ RTF to Plain Text issue I am having.

I have 3 instances on one SQL server. Only one of the Instances is not working. Even tried deleting everything and resetting it up.

Here is the code I am testing with:

USE Test_Database
GO
DECLARE @RTF varchar(max)
SET @RTF = '{
tfansiansicpg1252uc1deff0deflang1033{fonttbl{f0 Calibri;}{f1 Arial;}}{colortbl
ed0green0blue0 ;
ed255green255blue255 ;}viewkind4paperw12240paperh15840margl1425margr1425margt1425margb1425sectdpgwsxn12240pghsxn15840
marglsxn1425margrsxn1425margtsxn1425margbsxn1425pardfs21sl276slmult1sa180{f1fs21
Test New Progress Notes - Praveen}par}';
SELECT dbo.clr_fn_ConvertRTF2PlainText(@RTF)

Here is what I am getting back:

The operation completed successfully

Not sure what to check as the other two instances are working fine.

I have CLR enabled in sp_Configure

clr enabled 0 1 11

View 2 Replies View Related

SQL Queries - Kristen - In Plain English.

Oct 16, 2007

Good Morning Kristen:

Thank You, You are the first person, that has given me a clear answer. I am going to explain in plain English What this SP is suppose to do.

I have to find any station_nbr that has more than one group_code and that the current date = (DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-0-datepart(day,0),getdate())),0) The current date starts with the first day of the previous month.
Example today is October 10, 2007 I need September 01, 2007 for this query.
To insert the record it needs to be active meaning that the current date is in between Beg_eff_date and end_eff_date, and the end_eff_date is = ‘12/31/3000’


I need to write SP(cursor) that reads line by line this table and that insert the answer: INSERT INTO #TP_TIES_OVerlaping_Dates.

Example:
Current_date: 09/01/2007


Group_Code Station_nbr beg_eff_date end_eff_date
0000003 00371101 03/12/2006 12/31/3000
0000016 00371101 07/08/2007 12/31/3000


The above explanation is for the first type of Error.

The second Type of Error:
If the Group Code ends with "W" and the End_effective_date = '12/31/3000' and the W_Beg_date is Part of the Temp Table(I to create the W_Beg_Date Column) -- is GREATER than the End_eff_date. I want to Insert this Record into the Table. I need to set sometype of Record Control to make the different between one error to another one.

I thank you so much for your help.....I hope this is clear to understand for everybody.

End

View 7 Replies View Related

SQL Function To Convert RTF To Plain Text

Jul 23, 2005

Does anyone have the logic to convert RTF formatted data in a textcolumn into plain ascii text that I can use in a varchar variable orfield?We have an app that allows formatted comments/notes to be stored in aSQL 2000 text column. Ideally, I would like a trigger that would copyany inserted comments into a seperate table, varchar field so thatthese are viewable in other apps that can not display formatted RTF.I would really like the logic to be all SQL based and not have to use aclient app to read/convert/insert the data in a batch mode.

View 1 Replies View Related

Xp_smtp_sendmail Html+plain Text

Jul 20, 2005

Hi all. Iv'e tryed out xp_smtp_sendmail, and I like what I can see sofar. The thing I wonder about is if the xp supports sending both htmlAND plain text in the same mail. I'm on a sql2000 sp3 and I have noproblem with the xp when i either send plain text or html, but asstated above I need to send with both formats in one mail......For use when one does not know if the receiver uses a mail-client thatsupports html or not. If not does anyone know of a good way to attackthis problem?thanks in advanceKarl B

View 1 Replies View Related

Calling Stored Procedures Using Plain ADO In C#

Sep 7, 2006

Hello All,

We are trying to figure out how to make a stored procedure call and pass some inputs using C# and plain ADO. We are able to call an empty stored procedure but cannot pass parameters into a stored procedure.

Does anyone have sample code in C# whereby we can open a connection and pass inputs into a stored procedure? The following is a sample code we are using:


ADODB.Parameter param = new ADODB.Parameter();

param=cmd.CreateParameter("@StoreID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput,sizeof(int) , 9);

cmd.Parameters.Append(param);

          ADODB.Recordset rsInv = cmd.Execute(out objAffected, ref objAffected,-1 );

In the above example, we create a parameter of type integer and try to add it to the ADO Command object. It is supposed to return a recordset. It returns a recordset with record count -1.

 

Sincerely,

Dwight Kulkarni

View 3 Replies View Related

Joins On Views That Are Formed With Outer Joins

Nov 3, 2000

We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.

If the delete is recoded to use the join key word instead of the = sign
then it alway gives error 4425.


625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033
4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators.
The delete with a correleted sub query instead of a join works.

Error 4425 text would imply that joins with view formed by outer joins should be avoided.

Any ideas on the principles involved here.

View 1 Replies View Related

Microsoft Great Plain Integration Application And New Tables On Sql

Jul 28, 2006

In dexterity iam using table_compare() function to check if tables in datasource and dictionary are same but its not picking up the primary key change but it does pick up the column change

any suggestion

thanks

View 1 Replies View Related

Is It Possible To Import Data From Plain Text File In Integration Services

Feb 21, 2008



hi,


I am new to Integration services.I have one query ,Is it possible to import the data from text file in integration services.
I know that we can import the data from excel sheet and we can export it to table.But my question is whether we can do the same thing from the text file.If anyone come acroos the same thing send u r possible answers.Your help is much appreciated.



Thanks in advance.

View 1 Replies View Related

JOINS To Sub-Queries -vs- JOINS To Tables

Aug 11, 2005

SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig

View 3 Replies View Related

Ansi Joins Vs. SQL Joins

Oct 12, 1999

Hi,

Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?

The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)

However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.

Angel

View 1 Replies View Related

UNION ALL, Joins And No Joins

Feb 29, 2008

I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,

Table A Contains a Main Image, this image is displayed in the results
Table B Contains an Icon, this image is displayed in the results
Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.

Select title, description, image from tableA
UNION ALL
Select title, description, icon as image from tableB
UNION ALL
title, description, ( inner Join SELECT top(1)
from imageTableC where imagetableC.FK = tableC.PK)
as image from tableC


Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.

View 14 Replies View Related

Improve My SQL

Mar 9, 2008

I have the following SQL, which works but I think it can be done simplier. I seem to have to group it by multiple columns, but I am sure there must be a way of grouping the results by a single column. Any Ideas?

Code:


SELECT count(order_items.order_id) as treenum, orders.order_id, orders.order_date,
orders.cust_order, orders.del_date, orders.confirmed, orders.del_addr
FROM orders, order_items
WHERE orders.order_id = order_items.order_id GROUP by orders.order_id, orders.order_date
, orders.cust_order, orders.del_date, orders.confirmed, orders.del_addr
ORDER BY orders.order_id DESC

View 2 Replies View Related

How To Improve This Transaction

Jul 12, 2006

Hello,
 
 
I have four different transactions such as below and I do one insert and one update in each transaction and it seem it is slow and creates deadlock with the user interface.
 
These transactions are performed against the tables that users are accessing with another user interface. I have following two questions:
 
1. T2.TextField1 and TextField2 = @TextField2 are Ok, Nok fields so I did not put index since only two distinct values. Should I put indexes on these fields?
 
2. Can I make this transaction let user interface do its task in case accessing the same rows, I can start transaction again but I do not want users get disturbed?
.
 
BEGIN TRANSACTION pTrans
BEGIN
   INSERT INTO T1
                      (fields)
   SELECT     (fields)
   FROM         T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
   WHERE (T2.TextField1 = @TrType AND T2.TextField2 = @TextField2)
 
   UPDATE    T2
   SET              TextField2 = 'Ok',  TextField2Date=@MyRunDateTime
   FROM         T2
   WHERE (TextField1 = @TrType AND TextField2 = @TextField2)
 
  IF @@ERROR <> 0
  BEGIN
     rollback transaction pTrans
     return(-1)
  END
  ELSE
  BEGIN
     commit transaction pTrans
  END
END

View 3 Replies View Related

How To Improve This Query, Thanks

Nov 29, 2006

I have a pretty good db server with four CPUs, it has not any other loads on it, but the following query takes 4ms to return. I use the syscolumns this way quite often, I am not sure why it takes it that long to return, any idea? 
select 'master',id,colid,name,xtype,length,xprec,xscale,status from [ablestatic].[dbo].syscolumns where id=(select id from [ablestatic].[dbo].sysobjects where name='link_data_ezregs')
 

View 6 Replies View Related

Way To Improve SQL Skills

Jul 11, 2006

hi, have a nice day to all , can some expert here point out what are the do and don't when we writing query ? ie:should we create many view as handler to get data? when should we use store procedure and trigger ? and so on

i seeking the way to improve my sql skill, can someone suggest some reference site or material or book as well ?

thank you very much for helping

View 2 Replies View Related

Improve This Function

Nov 30, 2005

I wrote the following function a few years ago - before I learned about SQL's PATINDEX function. It might be possible to check for a valid email address syntax with a single PATINDEX string which could replace the entire body of hte function below.

Is anyone is interested in taking a crack at it?

Signed... lazy Sam

CREATE FUNCTION dbo.EmailIsValid (@Email varchar (100))
/*
RETURN 1 if @Email contains a valid email address syntax, ELSE RETURN 0
*/

RETURNS BIT
AS
BEGIN
DECLARE @atpos int, @dotpos int

SET @Email = LTRIM(RTRIM(IsNull(@Email, ''))) -- remove leading and trailing blanks

IF LEN(@Email) = 0 RETURN(0) -- nothing to validate

SET @atpos = charindex('@',@Email) -- position of first (hopefully only) @

IF @atpos <= 1 OR @atpos = LEN(@Email) RETURN(0) -- @ is neither 1st or last or missing

IF CHARINDEX('@', @email, @atpos+1) > 0 RETURN(0) -- Two @s are illegal

IF CHARINDEX(' ',@Email) > 0 RETURN(0) -- Embedded blanks are illegal

SET @dotpos = CHARINDEX('.',REVERSE(@Email)) -- location (from rear) of last dot

IF (@dotpos < 3) or (@dotpos > 4) or (LEN(@Email) - @dotpos) < @atpos RETURN (0) -- dot / 2 or 3 char, after @

RETURN(1) -- Whew !!

END
Go

View 8 Replies View Related

How Can I Improve My SQL Query

Aug 21, 2007

Hi,


I have this SQL query that can take too long time, up to 1 minute if table contains over 1 million rows. And if the system is very active while executing this query it can cause more delays I guess.


select
distinct 'CONV 1' as Conveyour,
info as Error,
(select top 1 substring(timecreated, 0, 7) from log b where a.info = b.info order by timecreated asc) as Date,
(select count(*) from log b where b.info = a.info) as 'Times occured'
from log a where loggroup = 'CSCNV' and logtype = 4


The table name is LOG, and I retrieve 4 columns: Conveyour, Error, Date and Times occured. The point of the subqueries is to count all distinct post and to retrieve the date of the first time the pst was logged. Also, a first and last date could be specified but is left out here.


Does anyone knows how I can improve this SQL query?


Best /M

View 6 Replies View Related

How To Improve This Transaction

Jul 12, 2006

Hello,


I have four different transactions such as below and I do one insert and one update in each transaction and it seem it is slow and creates deadlock with the user interface.

These transactions are performed against the tables that users are accessing with another user interface. I have following two questions:

1. T2.TextField1 and TextField2 = @TextField2 are Ok, Nok fields so I did not put index since only two distinct values. Should I put indexes on these fields?

2. Can I make this transaction let user interface do its task in case accessing the same rows, I can start transaction again but I do not want users get disturbed?
.

BEGIN TRANSACTION pTrans
BEGIN
INSERT INTO T1
(fields)
SELECT (fields)
FROM T2 INNER JOIN View1 ON T2.TrID = View1.MyTableID
WHERE (T2.TextField1 = @TrType AND T2.TextField2 = @TextField2)

UPDATE T2
SET TextField2 = 'Ok', TextField2Date=@MyRunDateTime
FROM T2
WHERE (TextField1 = @TrType AND TextField2 = @TextField2)

IF @@ERROR <> 0
BEGIN
rollback transaction pTrans
return(-1)
END
ELSE
BEGIN
commit transaction pTrans
END
END

View 3 Replies View Related

Multiple Left Joins (2 Left Joins 1 Big Headache)

Sep 1, 2005

Hi All,

Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.


My SQL statment is as follows :-
SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx

I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.

View 2 Replies View Related

How To Improve Update Speed

Dec 18, 2007

hi
  in asp.net,i used sql server to store records.
  in a table has 1 million records,but when i update the record,it is very slowly.
  is "create index" helpful for "update" operation?
  i need help,thanks a lot.

View 4 Replies View Related

Using SQL Profiler To Improve Performance

Mar 8, 2004

Hi,
We have a poorly performing SQL 2000 db. i have just defragged ( the HD, not indexes, these are done daily via SQL Agent) the data files of our server and have not found any improvement in response.
I have now got into using SQL profiler to analyse the server performance. in the results that the trace is returning there are some huge (REALLY BIG) values for the duration and cpu values but these rows have no textdata value returned (ie it is null)

why is this? for these rows, the reads and writes columns are also high.

if these rows are what is taking the cpu's time then how can i identify what the server is doing to make any changes?

any thoughts on what other values i might trace or what action i can take to find the slow down cause?

in performance manager the processors (dual Xeons) are rarely dropping below 60%.


thanks in advance

fatherjack

View 2 Replies View Related

Improve Query Time

Jul 7, 2006

Aside from indexes, will it help if I use multiple filegroups to improve the time needed to query millions of records?

View 2 Replies View Related

Improve Update Statement

Aug 31, 2006

is there anyway to improve this statement. make it short? any input will be appreciated.


update TempAddressParsingTable set ad_unit = case

when right(rtrim(ad_str1),3) like 'apt' or
right(rtrim(ad_str1),3) like 'lot'
and substring(reverse(ad_str1), 4,1) in ('', ',', '.')
then right(ad_str1,3)

when right(rtrim(ad_str1),4) like 'unit' or
right(rtrim(ad_str1),4) like 'apt%' or
right(rtrim(ad_str1),4) like 'lot%'
and substring(reverse(ad_str1), 5,1) in ('', ',', '.')
then right(ad_str1,4)

when right(rtrim(ad_str1),5) like 'unit%' or
right(rtrim(ad_str1),5) like 'apt%%' or
right(rtrim(ad_str1),5) like 'lot%%'
and substring(reverse(ad_str1), 6,1) in ('', ',', '.')
then right(ad_str1,5)

when right(rtrim(ad_str1),6) like 'unit%%' or
right(rtrim(ad_str1),6) like 'apt%%%' or
right(rtrim(ad_str1),6) like 'lot%%%'
and substring(reverse(ad_str1), 7,1) in ('', ',', '.')
then right(ad_str1,6)

when right(rtrim(ad_str1),7) like 'unit%%%' or
right(rtrim(ad_str1),7) like 'apt%%%%' or
right(rtrim(ad_str1),7) like 'lot%%%%'
and substring(reverse(ad_str1), 8,1) in ('', ',', '.')
then right(ad_str1,7)

when right(rtrim(ad_str1),8) like 'unit%%%%' or
right(rtrim(ad_str1),8) like 'apt%%%%%'
and substring(reverse(ad_str1), 9,1) in ('', ',', '.')
then right(ad_str1,8)

when right(rtrim(ad_str1),9) like 'unit%%%%%' or
right(rtrim(ad_str1),9) like 'apt%%%%%%'
and substring(reverse(ad_str1), 10,1) in ('', ',', '.')
then right(ad_str1,9)


when right(rtrim(ad_str1), 2) like '#%'
and substring(reverse(ad_str1), 3, 1) in ('', ',', '.')
then right(ad_str1, 2)
when right(rtrim(ad_str1), 3) like '#%%'
and substring(reverse(ad_str1), 4, 1) in ('', ',', '.')
then right(ad_str1, 3)
when right(rtrim(ad_str1), 4) like '#%%%'
and substring(reverse(ad_str1), 5, 1) in ('', ',', '.')
then right(ad_str1, 4)
when right(rtrim(ad_str1), 5) like '#%%%%'
and substring(reverse(ad_str1), 6, 1) in ('', ',', '.')
then right(ad_str1, 5)
else null
end

View 13 Replies View Related

Help Me To Improve My Application Performance

Mar 13, 2007

Dear Experts,
I'm a DBA, Working for a Product based company.
We are implementing our product for a certain client of huge OLTP.
our reports team is facing problem (error: all the reports are timed out).though the queries are written properly, Each query is taking some minutes of time.
I've given the command DBCC DROPCLEANBUFFERS.
the time immediately dropped to 10 sec.

now my question is :
please suggest me the DBCC commands or any DBA related commands to improve the performance of the application for my reports team.

Thanks in advance.

Vinod

View 4 Replies View Related

Performance And Database Improve Help

Jan 15, 2008

Hi All,

from your experience in SQL 2005 - do i have any free software that can help in improve performance or can help in identifying performance bottleneck. two examples of performance and help that i use usually use are the maintenance plan that do (check DB > reorganized index > rebuild index > update statics) and the second software is the SQL 2005 DASHBOARD for the reporting help.
do you have any other free tools and help that you can give me for performance or any thing that i must have in my SQL 2005 servers.

Thx

View 3 Replies View Related

Improve Performance In Query..

Jul 20, 2005

I have a table called work_order which has over 1 million records and acontractor table which has over 3000 records.When i run this query ,it takes long time since its grouping bycontractor and doing multiple sub SELECTs.is there any way to improve performance of this query ??-------------------------------------------------SELECT ckey,cnam,t1.contractor_id,count(*) as tcnt,(SELECT count(*) FROM work_order t2 WHEREt1.contractor_id=t2.contractor_id and rrstm=1 and rcdt is NULL) as r1,(SELECT count(*) FROM work_order t3 WHEREt1.contractor_id=t3.contractor_id and rrstm=2 and rcdt is NULL) as r2,(SELECT count(*) FROM work_order t4 WHEREt1.contractor_id=t4.contractor_id and rrstm=3 and rcdt is NULL) as r3,SELECT count(*) FROM work_order t5 WHEREt1.contractor_id=t5.contractor_id and rrstm=4 and rcdt is NULL) as r4,(SELECT count(*) FROM work_order t6 WHEREt1.contractor_id=t6.contractor_id and rrstm=5 and rcdt is NULL) as r5,(SELECT count(*) FROM work_order t7 WHEREt1.contractor_id=t7.contractor_id and rrstm=6 and rcdt is NULL) as r6,SELECT count(*) FROM work_order t8 WHEREt1.contractor_id=t8.contractor_id and rcdt is NULL) as open_count,(SELECT count(*) FROM work_order t9 WHEREt1.contractor_id=t9.contractor_id and vendor_rec is not NULL) asAck_count,(SELECT count(*) FROM work_order t10 WHEREt1.contractor_id=t10.contractor_id and (rtyp is NULL or rtyp<>'R') andrcdt is NULL) as open_norwoFROM work_order t1,contractor WHEREt1.contractor_id=contractor.contractor_id andcontractor.tms_user_id is not NULL GROUP BYckey,cnam,t1.contractor_id ORDER BY cnam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related







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