Hi,
I got a weird problem. I've created a sp that takes in the query analyzer 7 seconds to run. When i put in my code dataAdapter.Fill(dataSet.Tables(0)) it takes forever to finish!!
What's going on?
Any thoughts highly appreciated.
t.i.a.,
ratjetoes.
I need to display something like "Results x-y of z." The problem is, I can't figure out how to get the right value for z.I am using SqlDataAdapter and Fill()ing a DataSet with subset x through y of the result set. How can I get the right value for z?
View 5 Replies View RelatedI'm working in a ASP.NET 2.0 application with a SQL Server 2000 database on the back end. I have a strongly typed dataset in the application that calls a stored procedure for the select. I'm having trouble filling the dataset at runtime though.
I am trying to use a character string query because I setup different columns to be pulled from a table each time and in a different order so my T-SQL looks like this:
set @FullQuery = 'Select ' + @FieldsinOrder + ' from tblExample'exec (@FullQuery)
This works fine in query analyzer. The results return and display correctly. However, when I run the application, the dataset does not get filled. It is like the results do not output to the application.
If I change the query to be a normal select it works. For example:
select * from tblEmample
That works fine. What is it about a select query setup as a character string and then executed that ASP.NET doesn't like?
hello
I have been assigned the task to port some reports from Business Objects 5.0 to Reporting Services 2005.
The BO reports used data regions based on multiple datasets, which is not feasible in SSRS.
So I have to get everything in one big query. The problem is that the query runs for 2 hours instead of a couple of minutes with our old BO solution which got it's data from an access database !
is there a better way to do this ?
here is the query
Code Snippet
ALTER PROCEDURE [dbo].[MERCKGEN_HDMCKQ02_DoctorBrickDoctorGroups_values]
-- Add the parameters for the stored procedure here
@TimePeriodDesc varchar(255),
@TotalMarket varchar(255),
@datasetname varchar(255),
@VisibleProducts varchar(500),
@DoctorSubTerritory varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT xpospecialty.specialtydesc,
a.doctorsubterritory ,
a.doctorbrick ,
a.doctorgroup ,
xpomarket.productdesc ,
s.totalmarketrank ,
s.prod1ranktotal ,
s.prod2ranktotal ,
SUM
(
CASE
WHEN (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN xpomeasures.rxvalues
ELSE 0
END
) AS totalrxvalues,
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc <> 'Non -')
AND (XpoMarketSize.MarketSizeDesc <> 'Non - MKT')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS rxrcount,
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'High')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [high],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Medium')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [medium],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Low')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [low],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Very Low')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [very low],
COUNT
(
CASE
WHEN (xpoproductsize.prodsizedesc = 'Very High')
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
THEN 1
ELSE NULL
END
) AS [very high],
(SELECT SUM(xpomeasures.rxvalues) AS totalrxvalues
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures
ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.xpomarket
ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography
ON xpomeasures.geographyid = xpogeography.geographyid
WHERE (xpomarket.productdesc = @TotalMarket)
AND (xpomeasures.datasetname = @DatasetName)
AND (xpotimeperiod.datasetname = @DatasetName)
AND (xpomarket.datasetname = @DatasetName)
AND (xpogeography.datasetname = @DatasetName)
AND (xpogeography.doctorgroup = a.doctorgroup)
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
) AS rxvaluesdoctorgroup
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures
ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.XpoMarketSize
ON XpoMeasures.XpoMarketSizeId = XpoMarketSize.XpoMarketSizeId
INNER JOIN dbo.xpoproductsize
ON xpomeasures.xpoproductsizeid = xpoproductsize.xpoprodsizeid
INNER JOIN dbo.xpomarket
ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography a
ON xpomeasures.geographyid = a.geographyid
INNER JOIN dbo.xpospecialty
ON xpomeasures.xpospecialtyid = xpospecialty.xpospecialtyid
INNER JOIN dbo.Fn_mvparamsorted (@VisibleProducts,',' )
ON xpomarket.productdesc = fn_mvparamsorted.parame
LEFT JOIN (
SELECT xpogeography.doctorgroup,
SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END) AS totalrxvalues,
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = @TotalMarket THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS TotalMarketRank,
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'PPI Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod2RankTotal',
ROW_NUMBER() OVER (ORDER BY ROUND(SUM(CASE WHEN xpomarket.productdesc = 'Merck-Generics Total' AND xpogeography.doctorsubterritory = @DoctorSubTerritory THEN xpomeasures.rxvalues ELSE 0 END), 0) DESC) AS 'Prod1RankTotal'
FROM dbo.xpotimeperiod
INNER JOIN dbo.xpomeasures ON xpotimeperiod.timeperiodid = xpomeasures.timeperiodid
INNER JOIN dbo.xpomarket ON xpomeasures.xpomarketid = xpomarket.xpomarketid
INNER JOIN dbo.xpogeography ON xpomeasures.geographyid = xpogeography.geographyid
WHERE xpomeasures.datasetname = @DatasetName
and (xpomarket.datasetname = @DatasetName)
and (xpogeography.datasetname = @DatasetName)
AND xpotimeperiod.timeperioddesc = @TimePeriodDesc
GROUP BY xpogeography.doctorgroup
) AS s ON s.doctorgroup = a.doctorgroup
WHERE (xpomeasures.datasetname = @DatasetName)
AND (xpomarketsize.datasetname = @DatasetName)
AND (xpoproductsize.datasetname = @DatasetName)
AND (xpotimeperiod.datasetname = @DatasetName)
AND (a.datasetname = @DatasetName)
AND (XpoMarket.datasetname = @DatasetName)
AND (xpotimeperiod.timeperioddesc = @TimePeriodDesc)
AND (a.doctorsubterritory = @DoctorSubTerritory)
GROUP BY xpospecialty.specialtydesc,
a.doctorsubterritory ,
a.doctorbrick ,
a.doctorgroup ,
fn_mvparamsorted.parame ,
fn_mvparamsorted.sortid ,
xpomarket.productdesc ,
s.totalmarketrank ,
s.prod1ranktotal ,
s.prod2ranktotal
ORDER BY s.totalmarketrank,
a.doctorgroup ,
fn_mvparamsorted.sortid
END
We have a MS SQL Server 6.5 database table with 643,000 records.
There are several indexes including some clustered indexes.
We do a statement: update wo set udf3 = '1234567890123456' where woid = '123'
this returns immediately.
Then we try the same statement where the string is 1 character longer and it
takes 45 minutes to return. There is no indication of what the server is doing
during this time.
There is no index on UDF3 and WOID is the primary key.
Any suggestions what is happening? What can we do to correct it?
DBCC CheckTable finds no errors.
name rows reserved data index_size unused
-------------------- ----------- ------------------ ------------------ ------------------ ------------------
WO 643124 493418 KB 321580 KB 169824 KB 2014 KB
SQL Server 2000, QA Database: A table called Telephone_Directory with just 4.000 records.
SELECT * FROM Telephone_Directory is taking forever.
If I stop the select after 1 second I see 162 rows.
If I stop the select after 1 minute I see again 162 rows.
Why this could be happening?
The same querie on Production Database is taking 6 seconds to retrieve the 4.000 records.
I'm having a problem with an update operation in a stored procedure. Itruns so slowly that it is unusable, unless I comment a part out in whichcase it is very fast. However, I need the whole thing :). I have atable of email addresses of people who want to get invited to parties.Each row contains information like email address, city, state, country,and preferences for what types of events are of interest.The primary key is an EMAILID, and has a unique constraint on the emailfield. The stored procedure receives the field data as arguments, andinserts the record if the email address passed is not in the database.This works perfectly. However, if the stored procedure is called for anemail address that already exists, it updates the existing row insteadof doing an insert. This way I can build a web page that lets peoplemodify their preferences, opt in and out of the list and so on.If I am doing an update, the stored procedure runs SUPER SLOW (and thepage times out) unless I comment out the part of the update statementfor city, state, country and zipcode. However, I really need to be ableto update this!My database has 29 million rows.Thank you for telling me anything about how I can speed up this update!Here is the SQL statement to run the stored procedure:declare @now datetime;set @now = GetUTCDate();EXEC usp_EMAIL_Subscribe @Email='dberman@sen.us', @OptOutDate=@now,@Opt_GenInterest=1, @Opt_DatePeople=0, @Opt_NewFriends=1,@Opt_OldFriends=0, @Opt_Business=1, @Opt_Couples=0, @OptOut=0,@Opt_Events=0, @City='Boston', @State='MA', @ZCode='02215',@Country='United States'Here is the stored procedure:SET QUOTED_IDENTIFIER ONGOSET ANSI_NULLS ONGOALTER PROCEDURE [usp_EMAIL_Subscribe](@Email [varchar](50),@Opt_GenInterest [tinyint],@Opt_DatePeople [tinyint],@Opt_NewFriends [tinyint],@Opt_OldFriends [tinyint],@Opt_Business [tinyint],@Opt_Couples [tinyint],@OptOut [tinyint],@OptOutDate datetime,@Opt_Events [tinyint],@City [varchar](30), @State [varchar](20), @ZCode [varchar](10),@Country [varchar](20))ASBEGINdeclare @EmailID intset @EmailID = NULL-- Get the EmailID matching the provided email addressset @EmailID = (select EmailID from v_SENWEB_EMAIL_SUBSCRIBERS whereEmailAddress = @Email)-- If the address is new, insert the address and settings. Otherwise,UPDATE existing email profileif @EmailID is null or @EmailID = -1BeginINSERT INTO v_SENWEB_Email_Subscribers(EmailAddress, OptInDate, OptedInBy, City, StateProvinceUS, Country,ZipCode,GeneralInterest, MeetDate, MeetFriends, KeepInTouch, MeetContacts,MeetOtherCouples, MeetAtEvents)VALUES(@Email, GetUTCDate(), 'Subscriber', @City, @State, @Country, @ZCode,@Opt_GenInterest, @Opt_DatePeople,@Opt_NewFriends, @Opt_OldFriends, @Opt_Business, @Opt_Couples,@Opt_Events)EndElseBEGINUPDATE v_SENWEB_EMAIL_SUBSCRIBERSSET--City = @City,--StateProvinceUS = @State,--Country = @Country,--ZipCode = @ZCode,GeneralInterest = @Opt_GenInterest,MeetDate = @Opt_DatePeople,MeetFriends = @Opt_NewFriends,KeepInTouch = @Opt_OldFriends,MeetContacts = @Opt_Business,MeetOtherCouples = @Opt_Couples,MeetAtEvents = @Opt_Events,OptedOut = @OptOut,OptOutDate = CASEWHEN(@OptOut = 1)THEN @OptOutDateWHEN(@OptOut = 0)THEN 0ENDWHERE EmailID = @EmailIDENDreturn @@ErrorENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOFinally, here is the database schema for the table courtesy ofenterprise manager:CREATE TABLE [dbo].[EMAIL_SUBSCRIBERS] ([EmailID] [int] IDENTITY (1, 1) NOT NULL ,[EmailAddress] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[OptinDate] [smalldatetime] NULL ,[OptedinBy] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[FirstName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[MiddleName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[LastName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[JobTitle] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CompanyName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[WorkPhone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[HomePhone] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[AddressLine1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[AddressLine2] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[AddressLine3] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[City] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[StateProvinceUS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_ASNULL ,[StateProvinceOther] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,[Country] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[ZipCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SubZipCode] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[GeneralInterest] [tinyint] NULL ,[MeetDate] [tinyint] NULL ,[MeetFriends] [tinyint] NULL ,[KeepInTouch] [tinyint] NULL ,[MeetContacts] [tinyint] NULL ,[MeetOtherCouples] [tinyint] NULL ,[MeetAtEvents] [tinyint] NULL ,[OptOutDate] [datetime] NULL ,[OptedOut] [tinyint] NOT NULL ,[WhenLastMailed] [datetime] NULL) ON [PRIMARY]GOCREATE UNIQUE CLUSTERED INDEX [IX_EMAIL_SUBSCRIBERS_ADDR] ON[dbo].[EMAIL_SUBSCRIBERS]([EmailAddress]) WITH FILLFACTOR = 90 ON[PRIMARY]GOALTER TABLE [dbo].[EMAIL_SUBSCRIBERS] WITH NOCHECK ADDCONSTRAINT [DF_EMAIL_SUBSCRIBERS_OptedOut] DEFAULT (0) FOR [OptedOut],CONSTRAINT [DF_EMAIL_SUBSCRIBERS_WhenLastMailed] DEFAULT (null) FOR[WhenLastMailed],CONSTRAINT [PK_EMAIL_SUBSCRIBERS] PRIMARY KEY NONCLUSTERED([EmailID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_WhenLastMailed] ON[dbo].[EMAIL_SUBSCRIBERS]([WhenLastMailed] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_OptOutDate] ON[dbo].[EMAIL_SUBSCRIBERS]([OptOutDate] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_OptInDate] ON[dbo].[EMAIL_SUBSCRIBERS]([OptinDate] DESC ) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_ZipCode] ON[dbo].[EMAIL_SUBSCRIBERS]([ZipCode]) ON [PRIMARY]GOCREATE INDEX [IX_EMAIL_SUBSCRIBERS_STATEPROVINCEUS] ON[dbo].[EMAIL_SUBSCRIBERS]([StateProvinceUS]) ON [PRIMARY]GOMeet people for friendship, contacts,or romance using free instant messaging software! See a picture youlike? Click once for a private conversation with that person!<a href="http://www.sen.us"><imgsrc="http://www.sen.us/mirror/SENLogo_62_31.jpg"></a>*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 9 Replies View RelatedI have a SQL 2005 & SQL 2000 server. I am attempting to execute a simple update statement, something that looks like:
update AD
set AD.SomeDate = getdate()
from [ServerX].DB.dbo.Table
where ColumnX = 'X'
ServerX is the SQL 2000 box.
ServerY is the SQL 2005 box. Server Y is where this statement is invoked from. (Not shown in statement).
I have a linked server set up.
When executed from the 2000 box, it runs in < 1 second.
When both environments are 2005 to 2005, it takes less than < 1 second.
I have a database that is about 300 gig. I am setting up replication to a reporting server. We are doing a series or mock loads and I will need drop the tables and reload the main database a few times before we go live. To do that I plan to stop replication and drop all the articles, drop the subscription, then load the new data, then reinitialize and restart replication.
The first time I tried to do this, when I drop the articles, it seems to be trying to "clean up" the distribution database on the reporting server and that is taking a couple of hours to do. The disruption database is about 40 gig.
Is this correct behavior in SQL2005 replication? Is there a way to avoid this? I have all the replication pieces scripted out and would like to just drop replication, reload, and then run my scripts to recreate replication. But this "clean up" is going to cause me a lot of headache if I don't figure out what is going on.
Am I going down the wrong road here? Is there an easier way to do this? Any comments would be great!!!!
Thanks in advance for any help.
Jim Youmans
St. Louis Missouri
Hello,
When I try to display HIstory for one of my Maintenance Plan, it takes forever to bring me those results back (up to 15-20 minutes). What can be the problem? What should I check?
I used a stored procedure in my report. If I run the sp in Management Studio (on my pc, database is on a sql server) it takes only several minutes; but from reporting services (also on pc) I put it in the data tab and execute it, it takes forever, actually never finish. I want to know why it's taking so long to execute it from reporting services while it returns data instantly from Mgt Studio. There is cursor in the sp. I don't know whether this is the culprit. Anyone knows why? Thanks!
Below is the sp.
--------------------------------------------------------------------
create proc [dbo].[p_national_by_week]
as
set nocount on
declare @s1 nvarchar(2000), @parmdefinition nvarchar(300), @rangestart smalldatetime, @rangeend smalldatetime
, @price_low money, @price_high money, @weekdate smalldatetime
declare c1 cursor for
--- GG change for reg dates.
select weekdate from vtRealEstate_RealtorListing_WeekDates
open c1
fetch from c1 into @weekdate
while @@fetch_status =0
begin
select @rangeend = @weekdate+7, @rangestart=@weekdate
select @s1 = N'
declare @mlsid_count int, @avg_price money, @avg_day_on_market int, @median_price money, @c1 int
select @mlsid_count=count(*), @avg_price=avg(CurrentPricefilter),
@avg_day_on_market=avg(datediff(dd, FirstListedDate, LastModifiedDate))
from vtRealEstate_RealtorListings
where ((FirstListedDate <= @rangeStart and LastModifiedDate >= @rangeStart) or
(FirstListedDate >= @rangeStart and FirstListedDate < @rangeEnd)
)
and currentpricefilter is not null
and mlsidfilter is not null
select @c1=@mlsid_count/2
set rowcount @c1
select @median_price = CurrentPricefilter from vtRealEstate_RealtorListings
where
((FirstListedDate <= @rangeStart and LastModifiedDate >= @rangeStart) or
(FirstListedDate >= @rangeStart and FirstListedDate < @rangeEnd)
)
and currentpricefilter is not null
and mlsidfilter is not null
order by currentpricefilter
insert report_detail_test (weekdate, mlsid_count, avg_price, median_price
, avg_day_on_market)
values(@weekdate, @mlsid_count, @avg_price, @median_price, @avg_day_on_market)
', @parmdefinition=N'@rangestart smalldatetime, @rangeend smalldatetime, @weekdate smalldatetime'
exec sp_executesql @s1, @parmdefinition, @rangestart=@rangestart, @rangeend=@rangeend
, @weekdate = @weekdate
fetch from c1 into @weekdate
end
select weekdate
, mlsid_count
, avg_price
, median_price
, avg_day_on_market
from report_detail_test
order by WeekDate
UPDATE CD SET col1=SR.col1,col2=SR.col2,col3=SR.col3,col4=SR.col4,col5=SR.col5,col6=SR.col6,col7=SR.col7,
col8=SR.col8,col9=SR.col9,col10=SR.col10
FROM LNKSQL1.db1.DBO.Table1 CD
join Table2 USRI on USRI.col00 = CD.col00
join table3 SR on USRI.col00 = SR.col00
Here, I'm trying to tun this from an instance and do a remote update. col00 is a primary key and there is a clustered index that exists on this column. When I run this query, it does a 'select * from tabl1' on the remote server and that table has about 60 million rows. I don't understand why it would do a select *... Also, we migrated to SQL 2005 a week or so back but before that everything was running smooth. I dont have the execution plan from before but this statement was fast. Right now, I can't run this statement at all. It takes about 37 secs to do one update. But if I did the update on a local server doing remote joins here, it would work fine. When I tried to show the execution plan, it took about 10 mins to show up an estimated plan and 99% of the time was spent on Remote scan. Please let me know what I can do to improve my situation. Thank you
I have a row that is being used log track plays on our website.
Here's the table:
CREATE TABLE [dbo].[Music_BandTrackPlays](
[ListenDate] [datetime] NOT NULL DEFAULT (getdate()),
[TrackId] [int] NOT NULL,
[IPAddress] [varchar](20)
) ON [PRIMARY]
There's a CLUSTERED INDEX on ListenDate ASC and a NON CLUSTERED INDEX on the TrackId.
I have a TRIGGER on the Music_BandTrackPlays table that looks like the following:
CREATE TRIGGER [trig_Increment_Music_BandTrackPlays_PlayCount]
ON [dbo].[Music_BandTrackPlays] AFTER INSERT
AS
UPDATE
Music_BandTracks
SET
Music_BandTracks.PlayCount = Music_BandTracks.PlayCount + TP.PlayCount
FROM
(SELECT TrackId, COUNT(*) AS PlayCount
FROM inserted
GROUP BY TrackId) AS TP
WHERE
Music_BandTracks.TrackId = TP.TrackId
When a simple INSERT statement is done on the Music_BandTrackPlays table, it can take quite a long time. When I remove the TRIGGER the INSERTs are immediate. The Execution plan for the TRIGGER shows that a 'Inserted Scan' is taking up most of the resources.
How exactly is the pseudo 'inserted' table formed?
For now, I think the easiest thing to do is update my logging page so it performs 2 queries. One to UPDATE the Music_BandTracks table and increment the counter, and perform the INSERT into the Music_BandTrackPlays table seperately.
I'm ok with that solution but I would really like to understand why the TRIGGER is taking so long. The 'inserted' pseudo table will be 1 row 99% of the time. Does SQL Server perform a table scan on all 20 million rows in order to determine what's new and put it in the inserted pseudo table?
Thanks!
I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?
The table definition is displayed below.
CREATE TABLE [dbo].[AuditLogDetails](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordID] [int] NOT NULL,
[TableName] [varchar](64) NOT NULL,
[Modifications] [xml] NOT NULL,
CONSTRAINT [PK_AuditLogDetails] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The view definition is displayed below.
ALTER VIEW [dbo].[vwAuditLogDetails] WITH SCHEMABINDING
AS
SELECT P.ID,D.RecordID, dbo.f_GetModification(D.Modifications,P.ID) AS Modifications
FROM dbo.AuditLogParent P
INNER JOIN dbo.AuditLogDetails AS D ON dbo.f_GetIfModificationExist(D.Modifications,P.ID)=1
The definition for UDF f_GetModification
ALTER function [dbo].[f_GetModification]( @Modifications xml,@PID uniqueidentifier )
returns xml
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.query('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The definition for UDF f_GetIfModificationExist
ALTER function [dbo].[f_GetIfModificationExist]( @Modifications xml,@PID uniqueidentifier )
returns Bit
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.exist('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The Statement to create the index is below.
CREATE UNIQUE CLUSTERED INDEX [IX_ID_RecordID] ON [dbo].[vwAuditLogDetails]
(
[ID] ASC,
[RecordID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Can a SqlDataSource be used to fill a DataSet? If so, will you show me how it's done?
View 2 Replies View RelatedI'm trying without success to do a select command against the dataadapter and then fill a dataset. I'm using VS 2008, and I've set my project to the 2.0 framework. What is wrong is I get an empty dataset (The count on the dataset = 0. )with the code below. The database is connecting fine, so I assume it's something to do with the commandtext. The database is SQL CE 3.5.
Any ideas would be appreciated.
Code Snippet
Public Shared Function Init() As DataSet
'Todo: need to encrypt the password
Dim instance As New SqlCeConnection("Data Source=|DataDirectory|PBdata.sdf")
Dim selectcmd As SqlCeCommand = instance.CreateCommand
selectcmd.CommandText = "Select * From [dusers]"
Dim adp As New SqlCeDataAdapter(selectcmd)
Dim ds As New DataSet("dusers")
adp.Fill(ds)
Return ds
End Function
A - Must Declare variable @ZipEntry - error generates when I run the code below. If I hard code the "WHERE ZipCode = 72364" it works (display messed up but it works - don't know how to code in the line breaks yet). But when I try to pass in the value of a TextBox called txtZipEntry on the page I get this error. Am I not delcaring @ZipCode in the correct spot ?
Thank you,
public partial class ContractorResultsDisplay : System.Web.UI.Page{string ZipEntry;protected void Page_Load(object sender, EventArgs e){
ZipEntry = txtZipEntry.Text;SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["localhomeexpoConnectionString2"].ConnectionString);string sql = "Select [City], [StateCode], [AreaCode], [Latitude], [Longitude], [ZipCode] From [ZipCensus11202006] Where ZipCode > @ZipEntry";SqlCommand cmd = new SqlCommand(sql, con);cmd.Parameters.AddWithValue("@ZipEntry", ZipEntry);SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();da.Fill(ds, "ZipCodeRefs");StringBuilder htmlStr = new StringBuilder("");
foreach (DataRow dr in ds.Tables["ZipCodeRefs"].Rows){htmlStr.Append(dr["City"].ToString());}
ZipContent.Text = htmlStr.ToString();
}
}
The following is NOT filling the dataset or rather, 0 rows returned.....The sp.....CREATE PROCEDURE dbo.Comms
@email NVARCHAR ,@num INT OUTPUT ,@userEmail NVARCHAR OUTPUT ASBEGIN DECLARE @errCode INT
SELECT fldNum, fldUserEmailFROM tblCommsWHERE fldUserEmail = @email SET @errCode = 0 RETURN @errCode
HANDLE_APPERR:
SET @errCode = 1 RETURN @errCodeENDGOAnd the code to connect to the sp - some parameters have been removed for easier read.....
Dim errCode As Integer
Dim conn = dbconn.GetConnection()
Dim sqlAdpt As New SqlDataAdapter
Dim DS As New DataSet
Dim command As SqlCommand = New SqlCommand("Comms", conn)
command.Parameters.Add("@email", Trim(sEmail))
command.CommandType = CommandType.StoredProcedure
command.Connection = conn
sqlAdpt.SelectCommand = command
Dim pNum As SqlParameter = command.Parameters.Add("@num", SqlDbType.Int)
pNum.Direction = ParameterDirection.Output
Dim pUserEmail As SqlParameter = command.Parameters.Add("@userEmail", SqlDbType.NVarChar)
pUserEmail.Size = 256
pUserEmail.Direction = ParameterDirection.Output
sqlAdpt.Fill(DS)
Return DS
Like I said, a lot of parameters have been removed for easier read.And it is not filling the dataset or rather I get a count of 1 back and that's not right.I am binding the DS to a datagrid this way....
Dim DScomm As New DataSet
DScomm = getPts.getBabComm(sEmail)
dgBabComm.DataSource = DScomm.Tables(0)
And tried to count the rows DScomm.Tables(0).Rows.Count and it = 0Suggestions?Thanks all,Zath
Hi,I am unable to load data in dataset via sqlconncetion object. Always i get error at ds.fill.Thanks in advance. PLS Help.Sudipta Datta www.geocities.com/dattasudipta
View 1 Replies View Relatedthis may seem like a real newbie question, but I got no clue how to solve this problem. When I was working in Visual Studio 2003, whatever I wanted to fill a dataset with data, I was using a SqlDataAdapter that I created like this adapTest.Fill(dsTest); But now, I cant seem to find the sqldataadapter anywhere, I can just create (on the visual interface of one of my page of my aspx project) dataset, but cant find anywhere a adapter to put an SQL instruction into...maybe theres a new way to do this in Visual Studio 2005 that I dont know about. Im not sure if I was clear enough, but what I want in the end is to be able to use my dataset like that dsTest.Tables.Rows[0]["a column"].ToString()by filling the dataset with data like I was able to do in VS2003. Thanks for taking the time to read this.
View 3 Replies View Related Hello,
I have been reading about how you shouldn't build dynamic SQL statements (see TextBox1.Text in line 3) and should use parameters instead, but I haven't yet found how to create a SELECT statement with a parameter that fills a dataset. If anyone can show me the correct way of doing this I would appreciate it so I can add it to my code snippets for proper coding practices. Thanks in advance for any assistance.
string strConnectionString = ConfigurationManager.ConnectionStrings["sqlConnectionString"].ConnectionString;
SqlConnection myConnection = new SqlConnection(strConnectionString);
-> string sqlSelect = "select * from customers where city = " + "'" + TextBox1.Text + "'";
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, myConnection);
DataSet ds = new DataSet();
myConnection.Open();
da.Fill(ds, "myDataset");
myConnection.Close();
jcfrasco
I have an off the wall situation that occurs when I try to fill a dataset that has already been filled with data. The issue with the DataSet in and of itself was a mistake I made and is not relevant to this forum.
What would appear to be relevant though, is the fact that after the exception was caught, I could not longer query data from the SQL Express database that was included in my project. In other words, when I tried to view the data in the IDE by selecting "Show Table Data," I received a message telling me that the query was running and eventually it timed out.
To fix the problem in my application, I obviously eliminated the bad code. But to be able to query the database again, I had to physically restart my computer (yes, I probably could have restarted the service, I know).
So my question is, has anyone run into this phenomenon before? If so, is there a way to prevent it other than the obvious, which is to clear the DataTables in my DataSet before filling them?
It's not that big a deal, but it is kind of a pain in the rear to have to restart everything because of a stupid coding mistake on the client side while I'm developing an app.
I have a Stored Procedure that has a query in it and it take 0 second and then a stored procedure that takes 16 seconds. From what I can tell they shoul be the same.
It doesn't recompile when i run the stored procedure, I checked that.
THis is so annoying. I have 3 ADO executes in my program. THe first one creates a view, the second one performs an outer join on that view and returns a result set, the third execute drops the aforementioned view. THe program that is using this is installed on about 200 computers scattered across Germany and Italy, all querying the same MSsql server 7.0. THe queries run quite quick when few users are actively using the program (after hours for example). however in the heat of the day performance goes up and down dramatically with identical queries taking from 1 to 20 seconds to return their result set. Now I initially thought 'bandwidth issue out of our server'. However I noticed that if I take those three queries and run them from the sql server enterprise manager( running on the same computer as the aforementioned program) then the queries run instantly and the data is in my result pane in less than 2 seconds ALWAYS....even when the program is dogging it with 20 second delays before the result set returns. I know it is hanging on the return of the result set as I put a stop after before each ADO execute in order to check which one was eating up my time. Why is there this dichotomy between running the queries from my enterprise manager versus running them from an ADO object. Both are using TCP/IP (no named pipes involved). I havent monkied with the attributes of the ADO result set so they are all set to default.
I have used the sql server profiler to trace these queries and they always run in less than 33 milliseconds. THe duration is also never more than 33 milliseconds. THis stinks of a network resource issue but what always leads me somewhere else is how consistent the performance of the enterprise manager is when it runs the exact same three queries.
Here is my slightly edited connection string
Public Const connection_string = "Provider=SQLOLEDB;Server=000.000.000.000;" & _
"User ID=johndoe;Password=janedoe;Network=dbmssocn;" & _
"database=fidojoe"
Here are the 3 ADO executes:
conn.Execute (sqlstr_create_view)
Set resultset1 = conn.Execute(sqlstr_get_providers_by_DMISID)
conn.Execute (sqlstr_drop_view)
Hi there !
Thanks for taking the time to read this thread.
I don't know whether anyone has this problem, but I am definitely not using the right keywords to search for a thread.
My situation is this...
I have a dataset that has values to fill cells to multiple tables in a report.
However, I only want to select specific data from the dataset to fill textboxes and others.
I cannot change the stored procedure, but the sample of the data is shown below:-
Row Stat Val
0 dtRpt1 02/01/2005
1 Value1 1
2 Value2 2000
3 dtMailSent 02/28/2005
4 Value3 0
5 Value4 5
6 Value5 658
I know it looks weird, but the row really represents which "row" or textbox is it to fill with the Val. The Stat Column is just a way to make sure that I am filling the right values.
so my new report would have multiple tables to denote different categories.
In my first table, I tried putting the cells as follows:-
(expressions are highlighted in italics and bold)
TextBox1 =IIF(Fields!Row.Value =0, Fields!Val.Value,"")
Table1
Column1
DetailRow1 =IIF(Fields!Row.Value =1, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =2, Fields!Val.Value,"")
Table2
Column1
DetailRow1 =IIF(Fields!Row.Value =3, Fields!Val.Value,"")
DetailRow2 =IIF(Fields!Row.Value =4, Fields!Val.Value,"")
DetailRow3 =IIF(Fields!Row.Value =5, Fields!Val.Value,"")
DetailRow4 =IIF(Fields!Row.Value =6, Fields!Val.Value,"")
I only expect this report to print out one page holding the previous values.
However, it ended up printing like this
----------------------------------------------------------
Table1
Column1
DetailRow1 1
DetailRow2
Column1
DetailRow1
DetailRow2 2000
Table2
Column1
DetailRow1 02/28/2005
DetailRow2
DetailRow3
DetailRow4
Table2
Column1
DetailRow1
DetailRow2 0
DetailRow3
DetailRow4
Table2
Column1
DetailRow1
DetailRow2
DetailRow3 5
DetailRow4
Table2
Column1
DetailRow1
DetailRow2
DetailRow3
DetailRow4 658
------------------------------------------------------
I tried putting it into the headerrows instead of DetailRows, and it ended up printing the last value.
Is there anyway to do this ? print all the values out in one table ? I tried using textboxes, but I think I got my expression wrong.
Is this the correct expression ?
=IIF((Fields!Row.Value,"Dataset") =1, (Fields!Val.value, "Dataset"), "")
and it give me an error
The value expression for the textbox €˜textbox5€™ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Appreciate any advice or suggestion for this scenario !
Thanks!
Bernard
I have the below query which returns thousands of records. can I optimize the returned result set faster without changing the structure of the database?
SELECT dbo.tblComponent.ComponentID, dbo.tblComponent.ComponentName, dbo.tblErrorLog.ShortErrorMessage, dbo.tblErrorLog.LongErrorMessage, dbo.tblErrorLog.LogDate, dbo.tblErrorLevel.Description,dbo.tblErrorLog.ErrorLogIDFROM dbo.tblErrorLevel INNER JOIN dbo.tblErrorLog ON dbo.tblErrorLevel.ErrorLevelID = dbo.tblErrorLog.ErrorLevelID INNER JOIN dbo.tblComponent ON dbo.tblErrorLog.ComponentID = dbo.tblComponent.ComponentID
Thanks.
I have following common table expression query which is taking like 15 hours to run. would someone suggest what can I do to speed this thing up..
; with
a as (select proj_id, proj_start_dt,proj_end_dt, case when charindex('.', Proj_ID) > 0 then left(Proj_ID, len(Proj_ID) - charindex('.', reverse(Proj_ID))) end as Parent_Proj_ID from ods32.dbo.Proj a), --add Parent_Proj_ID column
b as (select proj_id, proj_start_dt,proj_end_dt,Parent_Proj_ID from a where PROJ_START_DT is not null and PROJ_END_DT is not null --get all valid rows
union all
select a.Proj_Id, b.PROJ_START_DT, b.PROJ_END_DT, a.Parent_Proj_ID from b inner join a on b.Proj_Id = a.Parent_Proj_ID where a.PROJ_START_DT is null or a.PROJ_END_DT is null) --get all invalid children of valid rows and give them the dates of their parents
update a set PROJ_START_DT = b.PROJ_START_DT, PROJ_END_DT = b.PROJ_END_DT
from WPData a left outer join b on a.Proj_ID = b.Proj_ID -- join up and update
thanks
Back in the days of SQL 7.0 I used a lot of ODBC SELECT querying form VB applications, in which I implemented NOLOCK in order to prevent the primary business applications from being locked out of tables once the queries were run.
Now, quite a few years later, I'm busying myself converting a lot of old Access based forms and queries to TSQL on SQL-Server 2000, and wonder aimlessly why NOLOCK queries (simple select ones) are imensely slower than a standars select clause.
SELECT * FROM employees
This would be much much faster than the code below, but users would get "The current record could not be accessed, as it is being used by another user", evidently because I'm locking the record while producing the output.
SELECT * FROM employees (nolock)
So this could should - as I remember it - do a dirty read on table, not obstructing other users and give me a snapshot of date as they are, although they might be locked for edit.
Could anyone explain to me why the nOLOCK query fials to give me any output? It is as if the nolock request is waiting for the table/records to free? In which case I'll never be able to run a query.
Cheers in advance, Trin
USE GLPDEMO
GO
select t.name as TriggerName, ta.name as TableName, o.parent_obj
into GLPDemo.dbo.Temp_TablesAndTriggers
from sysobjects o inner join sys.triggers t
on t.object_id = o.id inner join syscomments c
on c.id = t.object_id inner join sys.tables ta
on ta.object_id = o.parent_obj
where xtype = 'tr' and c.text like '%Audit%'
DECLARE @DBTrigger as varchar(100), @DBTable as varchar(100), @exestr as varchar(100)
DECLARE TCursor CURSOR for
SELECT TriggerName, TableName from Temp_TablesAndTriggers
OPEN TCursor
FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable
WHILE @@FETCH_STATUS = 0
select @exestr = ' DISABLE TRIGGER GLPDemo.dbo.' + @DBTrigger + ' ON GLPDemo.dbo.' + @DBTable
EXECUTE sp_executesql @exestr;
FETCH NEXT FROM TCursor
INTO @DBTrigger, @DBTable
CLOSE TCursor
DEALLOCATE TCursor
--DROP TABLE #Temp_TablesAndTriggers
Hi all,
I know this sounds rather dumb but my select statement is running forever!
I am trying to execute the statement through my C# code.
If I try to run the query through sql server management studio, sometimes it runs fine, but sometimes it keeps running and never returns a value.
I am doing something like this:
###########################################################################################
String query = "SELECT studentID from StudentTable WHERE studentDeptID = '100' AND deptName = 'CS'";
SqlCommand command = new SqlCommand(query, connection);
Object myObject = command.ExecuteScalar();
############################################################################################
StudentTable contains roughly 1000 rows.
Somebody please help me out of this. Thanks in advance.
Surya
I've a complex view on a SQL 2014 Enterprise Edition. If I query the view with:
SELECT * FROM myComplexView
it takes 14 seconds to completes
if I want a subset of the result and I run the query with a WHERE clause:
SELECT * FROM myComplexView WHERE [Season]='A16'
The query never completes (I've waited 10 minutes and then cancelled the task).