Text Column Slows Down Select
Jul 20, 2005
Hi All,
We're running SQL Server 2000, SP3.
I have a stored procedure that consists of a single Select statement.
It selects a bunch of columns one of which is a column of data type
TEXT.
SP takes 30 sec to run which causes timeouts on the Front End.
When I comment out the Text column from the select it only takes 1
Sec.
Is there anything I can do about it? I know I can't index a Text
column. It's also not used in the where clause, so no need for
Full-Text Search.
But we absolutely have to have it in the Select clause.
Thanks for the help in advance.
~Narine
View 5 Replies
ADVERTISEMENT
Mar 31, 2008
Hi
We have a t-sql statement in a SP that generates on average between 50 €“ 60 rows of data, pretty small! The statement references a View, some tables and temporary # table which has been created in the SP.
Everything works a treat and runs sub second until you put a Insert Into in front of the above statement scenario. The SP then takes a about a minute to run which happens to be about the same amount of time to generate all the data in the View.
I have not attached T-Sql statement at this stage as it runs ok without the Insert Into but would be happy to post it if need be.
Anybody else ever had this problem?
We are using SQL Server 2005 SP2 64 bit.
Art99
View 7 Replies
View Related
Dec 20, 2005
Hello. I using a simply SELECT statement to retrieve some data from aSQL SERVER via an ODBC connection. I had to go from VARCHAR to TEXTbecause the amount of data. Anyway, my SQL statements worked just finewhen I was using VARCHAR, but now since I am using TEXT, I am onlyreceiving part of the content back. Do I have to do some sort ofspecial Casting or something if I want to get all the content back?It's over 8,000 characters. Thank you very much. I have racking mybrain on this for a while.
View 8 Replies
View Related
May 9, 2014
One purchased app stored user's multiple lines input into a varchar column in which including char(13) and char(10).
My app need to select this value and format to multiple lines text in one text box.
How to code to output it?
View 5 Replies
View Related
Aug 1, 2007
Hello,
I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.
View 6 Replies
View Related
Sep 20, 2007
Hi peeps,
We have just upgraded to Service Pack 4 on our SQL Server 2000.
We have had a DTS job that normally takes about four hours to complete(this dts job has been ok for the last three years).
However, after applying SP4, this DTS job now takes over 8 hours to complete.
There are no other processes running on the box and the box is a high end Dell machine with 8 Gig of RAM.
Any advice on this would be greatly appreciated.
Bal
View 14 Replies
View Related
Dec 17, 2007
Hi guys I am sitting and testing som variants of this simple SP, and I have an question that I couldent answer with google or any thread in this forum.
Perhaps I am doing something really easy completly wrong here.
Why does the local variables in the first code segment slow down the overall execution of the procedure?
Dont mind the logic why I have them there are only testing som things out.
If i declare two variables the same way:
DECLARE @v INT
SET @v = 100
When I use it in a WHERE CLAUSE:
...WHERE [V] BETWEEN @v AND @x)
Is there any different then
...WHERE [V] BETWEEN 100 AND 200)
Cant figure this out, why does it hurt the performance so bad? As a C# guy its the same thing ?
Thanks in advance
/Johan
Slow
ALTER PROCEDURE [dbo].[spStudio_Get_Cdr]
@beginDate DATETIME = null,
@endDate DATETIME = null,
@beginTime INT,
@endTime INT,
@subscribers VARCHAR(MAX),
@exchanges VARCHAR(MAX) = '1:',
@beginDateValue int,
@endDateValue int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @s INT
SET @s = @beginDateValue
DECLARE @e INT
SET @e = @endDateValue
print @s
print @e
DECLARE @exch TABLE(Item Varchar(50))
INSERT INTO @exch
SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item
DECLARE @subs TABLE(Item Varchar(19))
INSERT INTO @subs
SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item
SELECT [id]
,[Abandon]
,[Bcap]
,[BlId]
,[CallChg]
,[CallIdentifier]
,[ChgInfo]
,[ClId]
,[CustNo]
,[Digits]
,[DigitType]
,[Dnis1]
,[Dnis2]
,[Duration]
,[FgDani]
,[HoundredHourDuration]
,[Name]
,[NameId]
,[Npi]
,[OrigAuxId]
,[OrigId]
,[OrigMin]
,[Origten0]
,[RecNo]
,[RecType]
,[Redir]
,[TerId]
,[TermAuxId]
,[TermMin]
,[Termten0]
,[Timestamp]
,[Ton]
,[Tta]
,[Twt]
,[Level]
FROM
[dbo].[Cdr] AS C
WHERE
(C.[DateValue] BETWEEN @s AND @e)
AND
(C.[TimeValue] BETWEEN @beginTime AND @endTime)
AND
EXISTS(SELECT [Item] FROM @exch WHERE [Item] = C.[Level])
AND
(EXISTS(SELECT [Item] FROM @subs WHERE [Item] = C.[OrigId] OR [Item] = C.[TerId]))
END
Fast
ALTER PROCEDURE [dbo].[spStudio_Get_Cdr]
@beginDate DATETIME = null,
@endDate DATETIME = null,
@beginTime INT,
@endTime INT,
@subscribers VARCHAR(MAX),
@exchanges VARCHAR(MAX) = '1:',
@beginDateValue int,
@endDateValue int
AS
BEGIN
SET NOCOUNT ON;
DECLARE @exch TABLE(Item Varchar(50))
INSERT INTO @exch
SELECT Item FROM [SplitDelimitedVarChar] (@exchanges, '|') ORDER BY Item
DECLARE @subs TABLE(Item Varchar(19))
INSERT INTO @subs
SELECT Item FROM [SplitDelimitedVarChar] (@subscribers, '|') ORDER BY Item
SELECT [id]
,[Abandon]
,[Bcap]
,[BlId]
,[CallChg]
,[CallIdentifier]
,[ChgInfo]
,[ClId]
,[CustNo]
,[Digits]
,[DigitType]
,[Dnis1]
,[Dnis2]
,[Duration]
,[FgDani]
,[HoundredHourDuration]
,[Name]
,[NameId]
,[Npi]
,[OrigAuxId]
,[OrigId]
,[OrigMin]
,[Origten0]
,[RecNo]
,[RecType]
,[Redir]
,[TerId]
,[TermAuxId]
,[TermMin]
,[Termten0]
,[Timestamp]
,[Ton]
,[Tta]
,[Twt]
,[Level]
FROM
[dbo].[Cdr] AS C
WHERE
(C.[DateValue] BETWEEN @beginDateValue AND @endDateValue)
AND
(C.[TimeValue] BETWEEN @beginTime AND @endTime)
AND
EXISTS(SELECT [Item] FROM @exch WHERE [Item] = C.[Level])
AND
(EXISTS(SELECT [Item] FROM @subs WHERE [Item] = C.[OrigId] OR [Item] = C.[TerId]))
END
View 4 Replies
View Related
Feb 28, 2008
Hi MSDN PPL!
I have an SQL Server 2005 instance that slows down over time, she almost grinds to a halt. The data is being exposed via an ASP.Net 2.0 web interface. The web application gets slower and slower over a matter of days. If I restart the SQL Server process she comes back to life and starts serving as it should - nice and snappy.
The web application does not perform much writing to the DB, 90% of the time its just reading. The DB server is worked hard by a console application that produces data each day. This console app runs for about 30 minutes during which there is a lot of reading, processing and writing back to the DB as fast as the hardware will allow. Its this massive workload that is slowing the DB server.
This seems to be related to the amount of memory that SQL Server is using. When looking at Task Manager I can see that sqlservr.exe is using 1,878,904K, this figure continues to rise while the console app runs. I have seen it over 2 GB. When the console app finishes the memory is still allocated and performance is slow. This continues to get worse after a few days of processing.
The machine's specs are:
* Windows Server 2003 R2 Standard
* SQL Server 2005 Standard 9.00.3054.00
* Twin 3.2Ghz Xeons
* 3.5 Gb RAM
I plan to apply "Cumulative hotfix package (build 3152) for SQL Server 2005 Service Pack 2" in a blind hope to solve the problem.
Any suggestions?
Sorry if this is in the wrong place guys, couldn't find a general performance topic. Please move accordingly.
Thanks,
Matt.
View 3 Replies
View Related
Aug 4, 2015
I have a report that uses different datasets based on the year selected by a user.
I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.
I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.
How could I display data from the dataset a user selects via the year_id options?
View 4 Replies
View Related
May 21, 2007
I'm hoping someone will be able to point me in the right direction for solving this problem as i've come a bit stuck.
The Sql Server 2005 Stored Procedure runs in about 3 secs for a small table when run from SQL Management Studio (starting with dbcc freeproccache before execution) but times out when run through ADO.NET on .NET (45 sec timeout).
I've made sure the connection was closed prior to opening and executing the adapter. I'm a bit stuck as where to check next though.
Any ideas greatfully received, Thanks
View 6 Replies
View Related
Oct 26, 2007
Hi all,
In my project, I have a website and through that, I run my reports. But the reports take a lot of time to render. When I checked the profiler, it showed that the SP for the report is run around 4-5 times. Due to this, the report rendering takes a lot of time.
When, I ran the SP with the same set of Parameters in Query Analyser, it ran in around 18 seconds. But when I ran the report from web interface, it took around 3 minutes to completely show the data. And the SP has been run 5 times.
I am having serious problems with Report's performance because of this. Many a times, report just times out. I have set the timeout as 10 minutes. And because the Sp is run 5 times, the report times out, if there is huge amount of data.
Any help would be appreciated.
Thanks in advance.
Swati
View 5 Replies
View Related
May 12, 2008
Hi
Will change in index slows down queries in SQL Server2000 sp4.
Please help me
View 4 Replies
View Related
Mar 13, 2008
I have two tables - gift_cards and history - each related by a field called "card_number". This field is encrypted in the history table but not in the gift_cards table. Let's say the passphrase is 'mypassphrase'. The following query takes about 1 second to execute with a fairly large amount of data in both tables:
SELECT max([history].[date_of_wash]) AS LastUse
FROM gift_cards AS gc LEFT JOIN history
ON gc.card_number=CAST(DecryptByPassPhrase('mypassphrase', HISTORY.CARD_NUMBER) AS VARCHAR(50))
GROUP BY gc.card_number
When I use a declared variable to contain the passphrase, the same query takes over 40 seconds. For example,
declare @vchPassphrase as nvarchar(20)
select @vchPassphrase = 'mypassphrase'
SELECT max([history].[date_of_wash]) AS LastUse
FROM gift_cards AS gc LEFT JOIN history
ON gc.card_number=CAST(DecryptByPassPhrase(@vchPassphrase, HISTORY.CARD_NUMBER) AS VARCHAR(50))
GROUP BY gc.card_number
This query is part of a stored procedure and, for security reasons, I can't embed the passphrase in it. Can anyone explain the discrepancy between execution times and suggest a way to make the second query execute faster?
Thanks,
SJonesy
View 4 Replies
View Related
Dec 7, 2007
We are using an OLE DB Source for the Data Flow Source and OLE DB Destination for the Data Flow Destination. The amount of data being moved is about 30 million rows, and it is gather using a sql command. There is not other transformations in between straight from one to another. The flow starts amazingly fast but after 5 million rows it slows considerably. Wondered if anyone has experienced anything similar with large loads.
View 6 Replies
View Related
Jul 4, 2007
Hi,
We are running SQL Server 2005 Ent Edition with SP2 on a Windows 2003 Ent. Server SP2 with Intel E6600 Dual core CPU and 4GB of RAM. We have an C# application which perform a large number of calculation that run in a loop. The application first load transactions that needs to be updated and then goes to each one of the rows, query another table get some values and update the transaction.
I have set a limit of 2GB of RAM for SQL server and when I run the application, it performs 5 records update (the process described above) per second. After roughly 10,000 records, the application slows down to about 1 record per second. I have tried to examine the activity monitor however I can't find anything that might indicate what's causing this.
I have read that there are some known issues with Hyper-Threaded CPUs however since my CPU is Dual-core, I do not know if the issue applies to those CPUs too and I have no one to disable one core in the bios.
The only thing that I have noticed is that if I change the Max Degree of Parallelism when the server slows down (I.e. From 0 to 1 and then back to 0), the server speeds up for another 10,000 records update and then slows down. Does anyone has an idea of what's causing it? What does the property change do that make the server speed up again?
If there is no solution for this problem, does anyone know if there is a stored procedure or anything else than can be used programmatically to speed up the server when it slows down? (This is not the optimal solution however I will use it as a workaround)
Any advice will be greatly appreciated.
Thanks,
Joe
View 3 Replies
View Related
Jul 20, 2005
Hi everyone,I encountered an error "Need to run the object to perform this operationCode execution exception: EXCEPTION_ACCESS_VIOLATION" When I try to import data from Oracle to MS SQL Server with EnterpriseManager (version 8.0) using DTS Import/Export Wizard. There are 508 rowsin Oracle table and I did get first 42 rows imported to SQL Server.Anyone knows what does the above error message mean and what causes therest of the row failed importing?Thanks very much in advance!Rene Z.--Posted via http://dbforums.com
View 1 Replies
View Related
May 31, 2007
I have an SSIS Package which is designed to import log files. Basically, it loops through a directory, parses text from the log files, and dumps it to the database. The issue I'm having is not with the package reading the files, but when it attempts to write the information to the db. What I'm seeing is that it will hit a file, read 3000 some lines, convert them (using the Data Conversion component), and then "hang" when it tries to write it to the db.
I've run the SQL Server Profiler, and had originally thought that the issue had to do with the collation. I was seeing every char column with the word "collate" next to it. On the other hand, while looking at the Windows performance monitor, I see that the disk queue is maxed at 100% for about a minute after importing just one log file.
I'm not sure if this is due to the size of the db, and having to update a clustered index, or not.
The machine where this is all taking place has 2 arrays- both RAID 10. Each array is 600 GB, and consists of 8 disks. The SSIS package is being executed locally using BIDS.
Your help is appreciated!
View 2 Replies
View Related
Oct 31, 2002
Hi,
I have a table with col_noteText defined as 'Text' datatype column.
I want to search a pattern 'Lawyer' and replace with 'Attorney' in the column col_noteText.
Does anyone know how to do this for 'Text' datatype column.
Thanks in advance.
jfk
View 1 Replies
View Related
Jul 20, 2005
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View 5 Replies
View Related
Apr 9, 2008
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.
View 11 Replies
View Related
Apr 10, 2008
Using SQL Server 2000. How can I refer to one alias in another column?
E.g., (this a contrived example but you get the idea)
SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM data
Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.
View 14 Replies
View Related
Jul 20, 2005
Hello All,Is there a way to run sql select statements with column numbers inplace of column names in SQLServer.Current SQL ==> select AddressId,Name,City from AddressIs this possible ==> select 1,2,5 from AddressThanks in Advance,-Sandeep
View 1 Replies
View Related
Jan 2, 2004
i need help to get the value of a column which is selected from a column of another table....
View 3 Replies
View Related
May 19, 2015
i dont't know how to select row with max column value group by another column. I have T-SQL
CREATE PROC GET_USER AS
BEGIN
SELECT T.USER_ID ,MAX(T.START_DATE) AS [Max First Start Date] ,
MAX(T.[Second Start Date]) AS [Max Second Start Date],
T.PC_GRADE,T.FULL_NAME,T.COST_CENTER,T.TYPE_PERSON_NAME,T.TRANSACTION_NAME,T.DEPARTMENT_NAME ,T.BU_NAME,T.BRANCH_NAME,T.POSITION_NAME
FROM (
[code]....
View 3 Replies
View Related
Sep 22, 2000
This may seem easy for some but how do you sql select from a table a name with ' included. e.g. select everyone with the surname O'Brian
At the moment I am using Select * From Employees where [surname] = " xsurname " wher xsurname has the name.
View 1 Replies
View Related
Apr 21, 2008
I have a repository with about 10,000 records. These records are selectable by searching by author surname, this works fine, but. the powers that be would like the data to be searchable by phrase. The db lives on a commercial host where I'm unable to create full text search catalogues.
I'd like to be able to return all results that contain both terms, or either if someone types in (for example) Breast Cancer. The search will only select from the the one field which will contain complex phrases such as "Second-line treatment of postmenopausal women with advanced breast carcinoma"
Any help on creating a select query that will do this will be greatly appreciated...
View 1 Replies
View Related
Nov 10, 2007
Hi everyone,
You have helped me resolved my previous problem with the LIKE statement, and now I'm running into this TEXT STRING problem that I desperately need your help and guidance again.
The following is the set of various descriptions in a PRODUCT_DESC field. I need to be able to calculate the Squared Meter of these products. As you can see, I need to be able to extract the part in the middle (like 2x60YD, etc.) for each record and perform some sort of calculation and conversion. The problem is that I can't find a way to do this effectively. Can someone please help me? Thanks.
PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
CH PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
PG5...130 MASKING TAPE 2X60YD 6.4MIL IPG PREMIUM
PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM
PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM
PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
PG5...130 MASKING TAPE 2X60YD 6.4MIL IPG PREMIUM
PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM
PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
View 10 Replies
View Related
Jul 25, 2007
Hi,
I am mapping the .csv files to a destination table in sql server.
The names of each column in the .csv file is the same as the ones in the destination table.
In the destination table there is an extra field i.e. FileTypeField
Here is the question;
When transfering the data from .csv to the destination table i.e flat file source to oledb destination, I would like to place a text into the field FileTypeField
I do not see an option to do this. Can you help please?
Thanks
View 3 Replies
View Related
Feb 17, 2004
I want to know how to select a Column without selecting a column name
View 6 Replies
View Related
Jan 1, 2007
Is it possible to combine fields and text in a select statement?
In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:
SELECT DISTINCT GRP AS GroupName, "Year: " + YEAR + "Grade: " + GRD AS ShowMe
FROM GE_Data
WHERE (DIST = @DIST)
I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.
If this is possible, or there's a better way to do it, I'd love some more info.
Thanks!
-Mathminded
View 7 Replies
View Related
Jan 26, 2008
I'm trying to populate a table of pending emails. The problem is I need
to populate the email field using a select statement but the message
field with static text. Can this be done or is another approach more
prudent? What I have is below but is kicking errors:DECLARE @msg varchar(300) SET @msg = 'New users have applied for accounts. Please review their information.'IF @Type='CreateUserApply' INSERT INTO cdds_Email (Address,Message)VALUES (SELECT M.EmailFROMdbo.aspnet_Membership MINNER JOINdbo.aspnet_UsersInRoles UINNER JOINdbo.aspnet_Roles RON U.RoleId = R.RoleIdON U.UserId = M.UserIdWHERER.RoleName = 'Manager',@msg)
View 3 Replies
View Related
May 17, 2008
Hello, I'm learning about asp .net and I've created a simple application with SQLServer integration. I have 3 text boxes where I can insert text and then use them as parameters for the Select query string of a SqlDataSource which is then bound to a DataGrid.My question is: Is there any default configuration I can use with the SqlDataSource which do not add a given field as a parameter to the WHERE clause if the text box content is empty ? Sure I can sweep through all my text boxes, check if they're empty and dynamically build the WHERE clause with correct parameters, but is theres a built-in, more elegant way to solve this ?Thanks in advance.Arashikage
View 2 Replies
View Related
Dec 2, 2004
Hi all,
I have some bad data that i'm trying to do a workaround for. I have a comma delimited text file with a column which contains a few records that has, gasp, a comma in it. Thus creating an extra column and pushing out NULL values in an empty column that isn't recognized by my DTS package.
Can i fix this?
And if I'm correct in my assumption that I can't, how do I import only the fields preceding the bad data field? When i attempt to do that, I get the following message
'Too many columns found in the current row; non white-spaced characters found after the last defined column's data.'
Blindman, you probably know exactly what to do, huh?
View 14 Replies
View Related
