Retrieving data takes more than 5 minutes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Retrieving data takes more than 5 minutes

Author

Topic

celsius
Starting Member

26 Posts

Posted - 2006-02-13 : 01:54:51

Why does it take more than five minutes to retrieve data? Does the number of records in a table affect SQL Server's performance? I am trying to get the average data size of rows of my database's tables. I encounter the problem whenever I include the two tables, each with 2000 and 400 rows respectively.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-13 : 05:34:40

How about posting the problematic query?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-13 : 09:23:51

Bad SQL, lack of indexes, or both.

celsius
Starting Member

26 Posts

Posted - 2006-02-13 : 21:42:28

SELECT --AccessMobiles AVG
(
DATALENGTH( ISNULL(AM.AccessMobile, '') ) +
DATALENGTH( ISNULL(AM.Network, '') ) +
DATALENGTH( ISNULL(AM.SimType, '') )
) AS 'AccessMobiles',

--AdminReports AVG
(
DATALENGTH( ISNULL(AR.ReportID, 0) ) +
DATALENGTH( ISNULL(AR.ReportName, '') ) +
DATALENGTH( ISNULL(AR.[Description], '') )
) AS 'AdminReports',

--AgentAccountPaymentDetails
AVG
(
DATALENGTH( ISNULL(APD.[ID], 0) ) +
DATALENGTH( ISNULL(APD.HeaderID, 0) ) +
DATALENGTH( ISNULL(APD.DatePosted, GETDATE()) ) +
DATALENGTH( ISNULL(APD.Amount, 0) ) +
DATALENGTH( ISNULL(APD.Details, '') ) +
DATALENGTH( ISNULL(APD.Notes, '') ) +
DATALENGTH( ISNULL(APD.IsCleared, '') )
) AS 'AgentAccountPaymentDetails',

--AgentAccountPaymentHeaders
AVG
(
DATALENGTH( ISNULL(APH.[ID], 0) ) +
DATALENGTH( ISNULL(APH.AgentID, 0) ) +
DATALENGTH( ISNULL(APH.AmountLastDeposited, 0) ) +
DATALENGTH( ISNULL(APH.DatePosted, GETDATE()) ) +
DATALENGTH( ISNULL(APH.Balance, 0) )

) AS 'AgentAccountPaymentHeaders',

--Agents AVG
(
DATALENGTH( ISNULL(A.AgentID, 0) ) +
DATALENGTH( ISNULL(A.Salutation, '') ) +
DATALENGTH( ISNULL(A.LastName, '') ) +
DATALENGTH( ISNULL(A.FirstName, '') ) +
DATALENGTH( ISNULL(A.MiddleName, '') ) +
DATALENGTH( ISNULL(A.Gender, '') ) +
DATALENGTH( ISNULL(A.Birthdate, GETDATE()) ) +
DATALENGTH( ISNULL(A.MobileNum, '') ) +
DATALENGTH( ISNULL(A.LimitPerLoad, 0) ) +
DATALENGTH( ISNULL(A.TotalLimit, 0) ) +
DATALENGTH( ISNULL(A.Balance, 0) ) +
DATALENGTH( ISNULL(A.NetLimit, 0) ) +
DATALENGTH( ISNULL(A.Exposure, 0) ) +
DATALENGTH( ISNULL(A.[Password], 0) ) +
DATALENGTH( ISNULL(A.AccessMobile, '') ) +
DATALENGTH( ISNULL(A.IsDeleted, GETDATE()) )
) AS 'Agents',

--Batches AVG
(
DATALENGTH( ISNULL(B.BatchID, 0) ) +
DATALENGTH( ISNULL(B.CreateDateTime, GETDATE()) )
) AS 'Batches',

--BinaryVariables AVG
(
DATALENGTH( ISNULL(BV.AccessMobile, '') ) +
DATALENGTH( ISNULL(BV.Code, '') ) +
DATALENGTH( ISNULL(BV.Value, 0) )
) AS 'BinaryVariables',

--CallRegister AVG
(
DATALENGTH( ISNULL(CR.RegID, 0) ) +
DATALENGTH( ISNULL(CR.AccessMobile, '') ) +
DATALENGTH( ISNULL(CR.Mobile, '') ) +
DATALENGTH( ISNULL(CR.DBCallDateTime, GETDATE()) ) +
DATALENGTH( ISNULL(CR.NetworkCallDateTime, GETDATE()) ) +
DATALENGTH( ISNULL(CR.Type, '') )
) AS 'CallRegister',

--Categories AVG
(
DATALENGTH( ISNULL(C.CategoryID, 0) ) +
DATALENGTH( ISNULL(C.AccessMobile, '') ) +
DATALENGTH( ISNULL(C.CategoryName, '') ) +
DATALENGTH( ISNULL(C.IsDeleted, GETDATE()) )
) AS 'Categories',

--ContactAccountPaymentDetails
AVG
(
DATALENGTH( ISNULL(CPD.[ID], 0) ) +
DATALENGTH( ISNULL(CPD.HeaderID, 0) ) +
DATALENGTH( ISNULL(CPD.DatePosted, GETDATE()) ) +
DATALENGTH( ISNULL(CPD.Amount, 0) ) +
DATALENGTH( ISNULL(CPD.Details, '') ) +
DATALENGTH( ISNULL(CPD.Notes, '') ) +
DATALENGTH( ISNULL(CPD.IsCleared, '') )
) AS 'ContactAccountPaymentDetails',

--ContactAccountPaymentHeaders AVG
(
DATALENGTH( ISNULL(CPH.[ID], 0) ) +
DATALENGTH( ISNULL(CPH.ContactID, 0) ) +
DATALENGTH( ISNULL(CPH.AmountLastDeposited, 0) ) +
DATALENGTH( ISNULL(CPH.DatePosted, GETDATE()) ) +
DATALENGTH( ISNULL(CPH.Balance, 0) )
) AS 'ContactAccountPaymentHeaders',

--ContactClassificationProperties AVG
(
DATALENGTH( ISNULL(CCP.ContactID, 0) ) +
DATALENGTH( ISNULL(CCP.ContactClassificationsID, 0) )
) AS 'ContactClassificationProperties',

--ContactClassifications
AVG
(
DATALENGTH( ISNULL(CC.ContactClassificationsID, 0) ) +
DATALENGTH( ISNULL(CC.AccessMobile, '') ) +
DATALENGTH( ISNULL(CC.ClassificationName, '') )
) AS 'ContactClassifications',

--Contacts AVG
(
DATALENGTH( ISNULL(Co.ContactID, 0) ) +
DATALENGTH( ISNULL(Co.ContactType, '') ) +
DATALENGTH( ISNULL(Co.CreateDateTime, GETDATE()) ) +
DATALENGTH( ISNULL(Co.StartDate, GETDATE()) ) +
DATALENGTH( ISNULL(Co.AccessMobile, '') ) +
DATALENGTH( ISNULL(Co.Salutation, '') ) +
DATALENGTH( ISNULL(Co.FirstName, '') ) +
DATALENGTH( ISNULL(Co.MiddleName, '') ) +
DATALENGTH( ISNULL(Co.LastName, '') ) +
DATALENGTH( ISNULL(Co.Gender, '') ) +
DATALENGTH( ISNULL(Co.Birthdate, GETDATE()) ) +
DATALENGTH( ISNULL(Co.UnitNum, 0) ) +
DATALENGTH( ISNULL(Co.Street, '') ) +
DATALENGTH( ISNULL(Co.Barangay, '') ) +
DATALENGTH( ISNULL(Co.City, '') ) +
DATALENGTH( ISNULL(Co.Province, '') ) +
DATALENGTH( ISNULL(Co.Country, '') ) +
DATALENGTH( ISNULL(Co.Landline, '') ) +
DATALENGTH( ISNULL(Co.Mobile, '') ) +
DATALENGTH( ISNULL(Co.Email, '') ) +
DATALENGTH( ISNULL(Co.Company, '') ) +
DATALENGTH( ISNULL(Co.Branch, '') ) +
DATALENGTH( ISNULL(Co.Department, '') ) +
DATALENGTH( ISNULL(Co.SmartDealerCardNo, '') ) +
DATALENGTH( ISNULL(Co.SmartMoneyAcct, '') ) +
DATALENGTH( ISNULL(Co.BirthdayMessage, GETDATE()) ) +
DATALENGTH( ISNULL(Co.Remarks, '') ) +
DATALENGTH( ISNULL(Co.IsDeleted, GETDATE()) )
) AS 'Contacts'

FROM dbo.AccessMobiles AM

FULL OUTER JOIN dbo.AdminReports AR
ON (AM.AccessMobile IS NOT NULL) AND (AM.AccessMobile <> '')

FULL OUTER JOIN dbo.AgentAccountPaymentDetails APD
ON APD.[ID] IS NOT NULL

FULL OUTER JOIN dbo.AgentAccountPaymentHeaders APH
ON APH.[ID] IS NOT NULL

FULL OUTER JOIN dbo.Agents A
ON (A.AccessMobile IS NOT NULL) AND (A.AccessMobile <> '')

FULL OUTER JOIN dbo.Batches B
ON B.BatchID IS NOT NULL

FULL OUTER JOIN dbo.BinaryVariables BV
ON (BV.AccessMobile IS NOT NULL) AND (BV.AccessMobile <> '')

FULL OUTER JOIN dbo.CallRegister CR
ON (CR.AccessMobile IS NOT NULL) AND (CR.AccessMobile <> '')

FULL OUTER JOIN dbo.Categories C
ON (C.AccessMobile IS NOT NULL) AND (C.AccessMobile <> '')

FULL OUTER JOIN dbo.ContactAccountPaymentDetails CPD
ON CPD.[ID] IS NOT NULL

FULL OUTER JOIN dbo.ContactAccountPaymentHeaders CPH
ON CPH.[ID] IS NOT NULL

FULL OUTER JOIN dbo.ContactClassificationProperties CCP
ON CCP.[ContactID] IS NOT NULL

FULL OUTER JOIN dbo.ContactClassifications CC
ON CC.AccessMobile IS NOT NULL AND CC.AccessMobile <> ''

FULL OUTER JOIN dbo.Contacts Co
ON Co.Mobile IS NOT NULL

celsius
Starting Member

26 Posts

Posted - 2006-02-13 : 21:43:24

Works fine when Contacts table is not included.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-13 : 21:48:09

Holy crap!

Is this for real?

Have you considered that there *might* be a more efficient way to query these tables?

celsius
Starting Member

26 Posts

Posted - 2006-02-13 : 22:01:12

^Actually I'm still thinking of another way.

celsius
Starting Member

26 Posts

Posted - 2006-02-13 : 22:10:51

I considered using sp_spaceused procedure before but the data it returns is somewhat unreliable. It returns a value even the table has no records.

celsius
Starting Member

26 Posts

Posted - 2006-02-13 : 22:18:18

Suggestions?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-14 : 00:24:56

Oh my God. My eyes! My eyes! Make it go away!

Are you aware that, if you look closely enough, half-way through your code you will find a man in a hockey-mask sneaking up on a youthful Jamie Lee Curtis? Turn around! Run away! He's right behind you! Aaagh...I can't bear to watch!

Look, lets just set aside the SELECT portion of your statement, which is holds its own special terrors, and take a look instead at your liberal use of FULL OUTER JOINs. You have to understand that this:

FULL OUTER JOIN dbo.AdminReports AR
ON (AM.AccessMobile IS NOT NULL) AND (AM.AccessMobile <> '')

is not really a join at all. It's not even a full outer join, since the criteria in no way relates any record in one table with any specific record(s) in the other table. What you have is the dreaded cross-join (the TSQL equivalent of Michael Myers, and I don't mean the funny Michael Myers), which joins every record in Access Mobile with EVERY OTHER RECORD in AdminReports. And you do this multiple times with different table. You end up with, literally, an exponential explosion of data.
Step back a bit and try writing just a portion of your sql involving no more than three tables, and get that to work correctly efficiently. Post the code here for review, if you'd like. Then start adding in other tables to get additional information.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-14 : 00:30:21

OK, I said to set aside the SELECT portion, but I just gotta ask: WHY are you averaging the datalength of surrogate keys? Please explain what this is supposed to get you:

AVG
(
DATALENGTH( ISNULL(APH.[ID], 0) ) +
DATALENGTH( ISNULL(APH.AgentID, 0) ) +
DATALENGTH( ISNULL(APH.AmountLastDeposited, 0) ) +
DATALENGTH( ISNULL(APH.DatePosted, GETDATE()) ) +
DATALENGTH( ISNULL(APH.Balance, 0) )

) AS 'AgentAccountPaymentHeaders',

Understand that we are not trying to be harsh here. We want to help you. It's kind of like you just walked into the emergency room and the attending physician just sent a page out to the all the doctors saying "Ya'all gotta come see this!"

celsius
Starting Member

26 Posts

Posted - 2006-02-14 : 01:01:11

Feedbacks are WELCOME. You may post anything you have in mind. ^_^

I would like to forecast the size of my data in one year so I need to get the average data size of a row in each table.

celsius
Starting Member

26 Posts

Posted - 2006-02-14 : 01:07:54

What do you think?

Database size in one year = (Average data size of rows) * (# of transactions in a day) * 365

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-02-14 : 01:09:45

quote:
Originally posted by celsius

You may post anything you have in mind. ^_^

If I posted that I would have to ban myself

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

celsius
Starting Member

26 Posts

Posted - 2006-02-14 : 02:02:38

I'll post my modified script here as soon as Im finished.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-14 : 04:59:58

Maybe you wanted to use union instead of joins?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-02-14 : 10:33:07

I suggest you start a whole new thread titled "How do I determine the average data size of rows in my tables?" I bet there are simpler methods than the path you have chosen. I don't know any off the top of my head, but I bet someone on the forum does.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-14 : 11:05:06

There is some good irony here, though. If you need to be able to calculate the space needed going forward because of a tight budget, then you will find with this algorithm that in order for it to complete in less than a few days you will need to buy a new super-fast (expensive) CPU!

Word of advice: if you are writing a query and pulling totals from TableA, and you also want to display completely unrelated totals from TableB, do them each seperately. As MMarovic says, you can union the results together in the end:

SELECT 'Table 1' as TableName, (.. your crazy calculation here ..) as DiskSpace
FROM Table1
UNION ALL
SELECT 'Table 2' as TableName, (.. your next crazy calculation here..) as DiskSpace
...
etc

That gives you a nice row/column result with all of your data.

celsius
Starting Member

26 Posts

Posted - 2006-02-14 : 11:20:17

I'll try that one jsmith8858. Thanks for the replies.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-14 : 11:30:35

I just posted a script in the Script Library forum that will give you a pretty good analysis of the space a table is using with bytes per row for data, indexes, and combined.

Script to analyze table space usage:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2006-02-14 : 12:04:39

All the JOINs of the style of

FULL OUTER JOIN dbo.AgentAccountPaymentHeaders APH
ON APH.[ID] IS NOT NULL

are going to give Cartesian results, and need some attention.

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources