Hello,
I have a simple count(*) query on tableA that takes 1:30 min.
The table has a pk defined and there are only 250k records.
I have already tried count(1) which takes same amount of time.
What influences count(*) performance?
I need to update the columns to blanks if it has Nulls; Most of thetime we do not have Nulls and to check that in a 3.2 million table ittakes 2 minutes and 45 seconds. (To do select count(*) from <table>where <columnname> is Null) .I just cut and pasted a part from my stored procedure:EXEC ('IF (SELECT COUNT (*) FROM ' + @tblname + ' WHERE '+@columnname + ' is Null ) >0BEGINPRINT ''' + @columnName + ' has Null ''' +' UPDATE ' + @tblname +' SET ' + @columnName + ' = ''''WHERE ' + @columnName + ' is NullPRINT ''Updated ''END'Please let me know if there is a better way to do instead of doingcount (*).Thanks in advance!
View 5 Replies View RelatedHi guys,
I'm facing a performance issue with select count(*) table_name when performing pagination to my results. My actualy query joins 4 tables with proper PK and FK contrainsts and indices applied.
select top 100 * from table1
left outer join table2 on tid=rid
left outer join table3 on tid=aid
inner join shipment on sid=sid
where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')
The query above takes 200 ms to 600 ms to execute.
select count(*) from table1
left outer join table2 on tid=rid
left outer join table3 on tid=aid
inner join shipment on sid=sid
where datetime>=convert(datetime,'20070810 00:00:00') and datetime<=convert(datetime,'20070920 23:59:59')
The query above takes 2000ms to 4000 ms to run.
The total records that fulfill the where clause is approximately 5000 to 6000 records. I checked the execution plan but found that the server is actually utilizing the indices with operations like index scan and index seek.
Are there any other things that i can do to improve the counting of total records?
Thanks.
Hi all,
I have a simple cube with only one measure : "Amount", I have three dimensions Supplier, Category and Company.
I would like to display a matrix with Categories as rows, company as columns, the amount and the number of distinct suppliers who appaers for each category/company.
I tried to create a calculated measure like this:
Count(Crossjoin({[D Supplier].[Supplier Code].members}, {[Measures].[Amount]}), EXCLUDEEMPTY)
The result is OK, but when I add this calculated Measure to my query the execution time jumps from a few seconds (<10) to a few minutes. Am I missing something ?
Anyone have an idea to do this in a better way ?
Hello,
I have the following queries that run on a view called EntrySummary:
1)
exec sp_executesql N'SELECT COUNT (1) FROM [dbo].[EntrySummary] WHERE [EntrySummary].[SubmissionStatusID] = @SubmissionStatusID0 AND [EntrySummary].[CreatedBy] = @CreatedBy1',N'@SubmissionStatusID0 int,@CreatedBy1 nvarchar(20)',@SubmissionStatusID0=4,@CreatedBy1=N'domainaperson'
2)
exec sp_executesql N'SELECT COUNT (1) FROM [dbo].[EntrySummary] WHERE [EntrySummary].[CreatedBy] = @CreatedBy1 AND [EntrySummary].[SubmissionStatusID] = @SubmissionStatusID0',N'@SubmissionStatusID0 int,@CreatedBy1 nvarchar(20)',@SubmissionStatusID0=4,@CreatedBy1=N'domainaperson'
(The only difference between the two queries being the order of the where clauses)
Both return the correct answer (4144), but Query 2 takes 10-15 seconds whereas Query 1 takes < second.
If the same query is resubmitted several times, this doesn't affect the times.
(The vast majority of the records have a status of 4, but only about 3% will be created by the person).
Replacing Count(1) with count(*) makes both queries return quickly.
Is sp_executesql creating poor execution plans ? Can anyone explain this behaviour?
Regards,
AndyM
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
below data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Â
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales")
set saleset(buyerset)
set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4}
set finalset as exists(salest,custdimensionfilter,"Sales")
Set ProdIP as dimproduct.dimproduct.prod1
set Othersset as (cyears,ProdIP)
(exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
it will take 12 to 15 min to execute.
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
1. Use mssql server agent service to take the schedule
2. Use a .NET windows service with timers to call SqlClientConnection
above, which way would be faster and get a better performance?
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server with aparticular query. It would take approximately 22 seconds to return 100rows, thats about 0.22 seconds per row. Note: I ran the query in singleuser mode. So I tested the query on the Development server by taking abackup (.dmp) of the database and moving it onto the dev server. I ranthe same query and found that it ran in less than a second.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue is relatedto some external hardware issue like: disk space, memory etc. Or couldit be OS software related issues, like service packs, SQL Serverconfiguations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating system related issue.Any Ideas would help me greatly!Thanks,Brian T*** Sent via Developersdex http://www.developersdex.com ***
View 2 Replies View RelatedI use SQL 2000
I have a Column named Bool , the value in this Column is 0�0�1�1�1
I no I can use Count() to count this column ,the result would be "5"
but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid
as the True is 2 and False is 3
the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count
could it be done by Count()? please help.
thank you very much
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
View 5 Replies View RelatedThe following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select
-- count returns unit data
Count(substring(m.PTNumber,3,3)) as PTCnt,
-- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt,
-- attempting to calculate the percent by PTCnt/TotalCnt returns 0
(Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct
-- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
group by substring(m.PTNumber, 3,3)
order by AUPct Desc
Thanks. Dan
Hello Everyone,I have a very complex performance issue with our production database.Here's the scenario. We have a production webserver server and adevelopment web server. Both are running SQL Server 2000.I encounted various performance issues with the production server witha particular query. It would take approximately 22 seconds to return100 rows, thats about 0.22 seconds per row. Note: I ran the query insingle user mode. So I tested the query on the Development server bytaking a backup (.dmp) of the database and moving it onto the devserver. I ran the same query and found that it ran in less than asecond.I took a look at the query execution plan and I found that they we'rethe exact same in both cases.Then I took a look at the various index's, and again I found nodifferences in the table indices.If both databases are identical, I'm assumeing that the issue isrelated to some external hardware issue like: disk space, memory etc.Or could it be OS software related issues, like service packs, SQLServer configuations etc.Here's what I've done to rule out some obvious hardware issues on theprod server:1. Moved all extraneous files to a secondary harddrive to free up spaceon the primary harddrive. There is 55gb's of free space on the disk.2. Applied SQL Server SP4 service packs3. Defragmented the primary harddrive4. Applied all Windows Server 2003 updatesHere is the prod servers system specs:2x Intel Xeon 2.67GHZTotal Physical Memory 2GB, Available Physical Memory 815MBWindows Server 2003 SE /w SP1Here is the dev serers system specs:2x Intel Xeon 2.80GHz2GB DDR2-SDRAMWindows Server 2003 SE /w SP1I'm not sure what else to do, the query performance is an order ofmagnitude difference and I can't explain it. To me its is a hardware oroperating systemrelated issue.Any Ideas would help me greatly!Thanks,Brian T
View 2 Replies View RelatedHi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
Is there a difference in performance when using count(*) or count(columnname)?
View 10 Replies View RelatedI would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month],
FROM RECalendar
WHERE
(EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101)))
AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
Thank you for your assistance in advance.
Joel
I have an sql command for when you add a new name to the database it counts to see how many of the name entered to the textbox exist in the database. If the count is 0 then it will add the name to the table. Else it displays an error message.
This works fine for inserting a new name but on my update page where you may update the name I have the same code which on button click counts to see how many exist. But if you leave the textbox the same value and click the button the count obviously results in 1 and brings up an error message.
Is there a way I can do a count but not including the name that is currently the value of the textbox?protected void UpdateSharedArea(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnection(docShare_ConString);
//Count the amount of area names that are the same as typed by user
SqlCommand existCheck = new SqlCommand("SELECT COUNT(doc_area_name) FROM document_area WHERE doc_area_name = @doc_area_name", connection);
SqlParameter areaname = new SqlParameter("@doc_area_name", SqlDbType.VarChar);
areaname.Value = AreaText.Text;
existCheck.Parameters.Add(areaname);
connection.Open();
int count = (int)existCheck.ExecuteScalar();
connection.Close();
//If the area name does not exist within the table
if (count == 0)
{
//Update name Cheers, Mark
Hi,
This is my table structure ,
Name
John
John
Raj
John
Raj
From the above table i want to count the repeated name and my output should be
Name Count
John 3
Raj 2
since no of jone in my table is 3 and number of Raj is 2 .
how to write the query for this ?
I want to do something like this:
SELECT COUNT (SELECT TOP(10) * FROM MyTable order by Date Desc) FROM MyTable where User = 'Scott'
What I want is to return the number of affected rows where the column 'User' equals 'Scott'....But is should only check in the 10 latest inserted rows.....
Hope you understand what I mean...
'<%# Eval("Username") %>'
Hi: Everybody
Today is not my day, I am trying to get a Count(*) from a table (name: Photos), I can get the total number if like this: WHERE (u_username = 'jamest85' )
Now, I want replace the 'jamest85' to a value from a Label, like: Label1.text or '<%# Eval("Username") %>', but always has error, so can you please check for me, how to make it right?
Thanks.
Below is the code:
<asp:Label ID="Label1" runat="server" Text='<%# Eval("Username") %>'></asp:Label> <asp:SqlDataSource ID="SqlDataSource2"runat="server" ConnectionString="<%$ ConnectionStrings:myConString %>" SelectCommand="SELECT COUNT(*) AS TotalPhotos FROM Photos WHERE (u_username = 'jamest85' ) GROUP BY u_username"></asp:SqlDataSource>
Thank you very much
jamest85
Hello, I need to retrieve all records from a table named Blogs and the number of Posts associated with which Blog giving the name NumberOfPosts to that extra column. I have the following: SELECT b.*, p.COUNT(*) AS NumberOfPosts FROM dbo.Blogs b LEFT JOIN dbo.Posts p ON b.BlogId = p.BlogId I get the error: Incorrect syntax near '*'. Could someone, please, help me out? Thanks, Miguel
View 3 Replies View RelatedHi all, Im using ASP.Net.In that i have an sql count statement which returns the count of the total messages in a particular date. Dim dt As Date
smsuser = uname.SelectedItem.Text
d = day.SelectedItem.Value
m = mon.SelectedItem.Value
y = year.SelectedItem.Value
dt = dt.ToShortDateString.Concat(d, "/", m, "/", y)
Dim StrSql As String = "select Count(Message) from Message where UserName='" & smsuser & "' AND Date='" & dt & "'"
Dim cmd As New SqlCommand(StrSql, con)
Dim reader As SqlDataReader
Dim no As String
Try
con.Open()
reader = cmd.ExecuteReader
If reader.Read = True Then
count.Text = reader.GetValue(0)
End If The count is displayed in a text box. But, the sql statement is not giving the correct count.Wat is wrong? Pls help...
Dear SQL,
I want to count the number of records, so I tried this:
SELECT COUNT(*) AS RecordCount
FROM Categories
WHERE Active = 1
ORDER BY Show_Order ASC
But it gives me an error:
error 8126: Column name 'Categories.Show_Order' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.
How can I make it work ? (I must ORDER it...)
I have a table(sometable) in my db that looks like this:
user item
bob books
bob pens
bob frogs
jay pencils
rob cups
rob plantsI run this script:SELECT DISTINCT user AS users
FROM sometable to get:users
bob
jay
robHow would I get it to include the # of rows ror each user like this:
users number
bob 3
jay 1
rob 2Thanks in advance.
What is wrong with this query?
SELECT *, (SELECT Count(*) FROM TPlanObjects where FMainID=tpm.FID and FType='ARTICLE') as FArtCount, (SELECT Count(*) FROM TPlanObjects where FMainID=tpm.FID and FType='FILE') as FObjCount FROM tPlanMain as tpm
I am usint DTS to transfer tables into a text file. I need to get a count of the number of rows that I transfer into each text file. Is there any way or an easy way to accomplish this?
View 4 Replies View RelatedHello,
I'm looking for a mean to be able to count records and to select them without having to write two SELECT lines :
select count(*) AS VNBR from SSITE where...
select Lenom = upper(nom), LeSite = Site+Page... from SSITE where...
WHERE... are the same for the two SELECTS ! Is there a solution ?
Thanks for your help.
Laurent.
I have a table of issues with a column for issueID and another for issueType.
For each issuetype, I want to count how many issues are but those with no issues, I want it to return with a zero.
Is this possible?
Thanks,
Anthony
Hi Folks,
I'm having a problem generating a total figure from two seperate sums in a query. Basically what I need to do is generate the total of select count(*) from t1, select count from t2.
without using a temp table.
Thanks in advance
Sirius
I have to create a statement that will find the next highest number of a field that is stored as a char(5), like
' 2' and add a new record adding one to that max number. The field is padded with leading spaces. I know how to get the next number. The question is counting how many spaces I need (is it a 9 or a 10) and then stuffing the leading spaces in front of the new number.
I want to be able to tell how many rows of data I have in each table. I don't want all of the tables detail though. How do I do this?
Thanks,
Tracy
Thanks to Craig for helping me out with my first question. My next question is this. Is there a way to have it give me the counts for all tables without entering each tablename? I may have new tables every time and would like not to have to keep up with changing the script.
Thanks in advance,
Tracy