Re: Handling big tables - Database Discussion Boards

Re: Could anyone recommend an interactive application for mass data analysis?

bestbird778827-May-12 23:24

27-May-12 23:24

thank you
I will check it.
And I have 3 quesiton about Qlikview ,and I will appreciate if you give me some tips.
1.can Qlikview solve the problem above:to compute the product whose annual sales values are all among the top 100.
MSSQL data structure( sales table's fields): productID, time, value
SQL solution is as below:
---------------------------------------------------------
WITH sales1 AS (
SELECT productID, YEAR(time) AS year, SUM(value) AS value1
FROM sales
GROUP BY productID, YEAR(time)
)

SELECT productID
FROM (
SELECT productID
FROM (
SELECT productID,RANK() OVER(PARTITION BY year ORDER BY value1 DESC) rankorder
FROM sales1 ) T1
WHERE rankorder<=100) T2
GROUP BY productID
HAVING COUNT(*)=(SELECT COUNT(DISTINCT year ) FROM sales1)

2.does Qlikview have stepwise ability. What I mean is like: step1. filter data. step2. group data on step1 . step3. filter data again on step2. the most important is: step4. sorting data on the result of step1 as a sub step. It's like what Excel does.

3.dees Qlikview support multiple datasets computation without SQL. what I mean is like: join statements in SQL .
thank you for your reply.

Conversion failed when converting the varchar value in SQLSERVER 2008

ChandrakanthGaddam22-May-12 5:47

ChandrakanthGaddam

22-May-12 5:47

Hi this is Chandra. i have one issue with Sql Stored procedure.

Please find the query below.

declare @Measure varchar(50)
declare @Time varchar(50)
declare @Manufacturer varchar(50)
declare @CategoryID int

SET @Measure ='Value'
SET @Time ='4 Weeks&'
SET @Manufacturer ='Jti Gallaher Ltd'
SET @CategoryID = 377;

Declare @SELECTtQuery1 varchar(1000)
Declare @FromQuery1 varchar(1000)
Declare @GROUPBYQuery1 varchar(1000)
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)

SET @FromQuery1 = 'FROM [dbo].[iView_Data_TCG_Aggregated_Product]
WHERE PeriodName = '''+@Time+'''
AND Manufacturer = '''+@Manufacturer+''' AND CategoryId = '''+ @CategoryID+

print @FromQuery1

When i execute this query i am getting the conversion error. PLease find the error below.

----------------------

Conversion failed when converting the varchar value ' FROM [dbo].[iView_Data_TCG_Aggregated_Product]
WHERE PeriodName = '4 Weeks'
AND Manufacturer = 'Test1' AND CategoryId = '' to data type int.

----------------------------------------------

I am not getting categoryid value when i execute. It is giving error.

Please suggest me wht to do.

Thanks
Chandrakanth

Re: Conversion failed when converting the varchar value in SQLSERVER 2008

scottgp22-May-12 6:02

scottgp

22-May-12 6:02

A few things:
- Since you're concatenating things together, @CategoryID will have to be converted to a string. Otherwise, SQL Server is trying to perform addition and convert the first variables to an int.

Try changing the SET to this:

SQL

SET @FromQuery1 = 'FROM [dbo].[iView_Data_TCG_Aggregated_Product]
WHERE PeriodName = '''+@Time+'''
AND Manufacturer = '''+@Manufacturer+''' AND CategoryId = '''+ cast(@CategoryID as varchar(10))

- There was a dangling plus sign I removed from what you posted just to get it to execute
- If this is going to be a valid query, your'e missing the SELECT clause with the columns to select.

Scott

Re: Conversion failed when converting the varchar value in SQLSERVER 2008

ChandrakanthGaddam22-May-12 6:20

ChandrakanthGaddam

22-May-12 6:20

Hi Scott,Thanks and lot. I got it. It is working fine from my end .

Thanks
Chandra

Handling big tables

V.21-May-12 23:28

21-May-12 23:28

My boss just tells me that I could have a table in the database that receives about 3x10^9 records a year (3 000 000 000). I could split this table in roughly four equal parts, but it still remains a lot. The user can choose to view just a day a year or even the entire history. (eg. the last 2 years wich still means 1 500 000 000 records)

I generally held a rule for myself to keep a production database around maximum 100 000 to 1 000 000 records total and put the rest in history, but this doesn't seem to be an option here and I'm very worried about the performance.

Has anyone got any experience with this size of database and how to solve the issue of getting the data quickly?

(Additonal info. The data are datapoints that need to be plotted on a graph in a certain time period. Performance is one of the top priorities.)

I see two major problems:
1. Keep table size reasonable, so how to split the data into different tables easily?
2. Getting the data to the user. (eg create average graph on the server, because a graph with more then, let's say 1000-2000 points is not useful on the screen anyway.)

Many thanks for any advice in advance.

(PS: Only thing I could find on google is splitting into smaller tables, but that doesn't immediately solve the problem of sending data to the user)

Re: Handling big tables

Mycroft Holmes21-May-12 23:56

Mycroft Holmes

21-May-12 23:56

Look into partitioning, probably by month.

Your users will NEVER look at 100k rows let alone 2 years worth. They may wish to analyse that volume but will now "view" them!

Never underestimate the power of human stupidity
RAH

Re: Handling big tables

V.22-May-12 0:01

22-May-12 0:01

Actually, what I think will happen is that the "view" on large amounts will be averaged. for example one point per month or week.

I do realize I'll need to talk to the boss. Big Grin | :-D

Re: Handling big tables

Mycroft Holmes22-May-12 14:15

Mycroft Holmes

22-May-12 14:15

Using SQL Server to deal with large volumes of data takes some serious planning, if you need to get into this area I would engage a professional, while I consider myself a good SQL developer I would not undertake such a project without professional support.

We had such a requirement and ended up using Oracle, it is faster and more robust but you HAVE to have a full time DBA.

Never underestimate the power of human stupidity
RAH

Re: Handling big tables

R. Giskard Reventlov22-May-12 6:26

R. Giskard Reventlov

22-May-12 6:26

Mycroft Holmes wrote:
Look into partitioning, probably by month.

Good call. Thumbs Up | :thumbsup:

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Re: Handling big tables

Eddy Vluggen22-May-12 9:32

Eddy Vluggen

22-May-12 9:32

V. wrote:
how to solve the issue of getting the data quickly?

Sql Server can handle millions of rows. ..but it won't perform at all if you put in a single scalar user-function in TSQL. As long as you're not doing a complex query, performance shouldn't be too much of a problem.

It might pay to create a test-environment with random data to identify bottlenecks as early as possible. With large amounts of data, I'd suggest large amounts of RAM (so that Sql Server can keep the large indexes in memory).

Bastard Programmer from Hell Suspicious | :suss:

Re: Handling big tables

jschell23-May-12 10:46

jschell

23-May-12 10:46

V. wrote:
The user can choose to view just a day a year or even the entire history. (eg.
the last 2 years wich still means 1 500 000 000 records)

Nonsense.

Simple math will demonstrate that a user (person) will NEVER look at that many records at once.

They will either look at a summary, or a summary with drill downs or a will be looking for a very, very small subset of that.

V. wrote:
Has anyone got any experience with this size of database and how to solve the
issue of getting the data quickly?

Get real requirements.

V. wrote:
The data are datapoints that need to be plotted on a graph in a certain time
period.

Excellent example. Graph 1.5 billion data points (from your first requirement) - so xactly how many pixels are on your screen? Again simple math will demonstrate that you can't view that many data points on a graph. So either there will be a much smaller time period or a summary of the entire period.

If a summary one solution is to build summary tables. So for example at the end of every data you create summary data of the day. Then a graph that display every day, uses the summary table rather than the raw data.

V. wrote:
1. Keep table size reasonable, so how to split the data into different tables
easily?

You start with real usage patterns. For example what percentage of the time does a user want to look at data for the last week, month, year? Or hour, day, week? Or by collection point via week. Etc, etc, etc.

Second you get realistic estimates and anticipated growth. So is 3 billion that average or the maximum? Will it be 3 billion next year or 30 billion. Keep in mind it must be realistic, not pie in the sky.

Third how long must you keep it? 1 year, 10 years 100 years?

And if it no one seems willing to discuss reality then you might want to look into price tags for a really hug SAN system, and submit a request to buy it because you will need it for testing - because the system will need that to run.

Re: Handling big tables

V.23-May-12 20:45

23-May-12 20:45

jschell wrote:
Excellent example. Graph 1.5 billion data points (from your first requirement)
- so xactly how many pixels are on your screen?

In meantime we already got here and I told my boss we need to downsample the information. Smile | :)

erd concepts

mido_h_8921-May-12 22:34

mido_h_89

21-May-12 22:34

when designing the erd , let's consider the following case :
we have two entities : (management_board) and (person) , a person can't be a member in more than one management board at once , but he can be a member in management_board_1 in 2011 , and then a member in management_board_2 in 2012 , so , the relation between entities : (management_board) and (person) , is it (one to many) or (many to many) ?

Re: erd concepts

Mycroft Holmes21-May-12 23:53

Mycroft Holmes

21-May-12 23:53

If he can be in only 1 MB at a time then it is one to many.

If you need a history of membership then it requires a different design, either m-m or an archive record.

Never underestimate the power of human stupidity
RAH

Error while declaring a cursor

VC_RYK21-May-12 19:16

VC_RYK

21-May-12 19:16

Hi All,

I am Newbie to Database and want to have all tables name and then columns of respective tables using following lines.

DECLARE CURSOR TEMPCURSOR is Select table_name from all_tables where owner='FSS_ADMIN' order by TABLE_NAME;
begin
open TEMPCURSOR;
for P in TEMPCURSOR loop
select COLUMN_NAME from user_tab_columns where table_name= p.table_name;
end loop;
end;

But while executing above lines, following error is occuring

Error starting at line 1 in command:
DECLARE CURSOR TEMPCURSOR is Select table_name from all_tables where owner='FSS_ADMIN' order by TABLE_NAME;
begin
open TEMPCURSOR;
for P in TEMPCURSOR loop
select COLUMN_NAME from user_tab_columns where table_name= p.table_name;
end loop;
end;
Error report:
ORA-06550: line 5, column 5:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

I googled to understand the issue but could not get so I request you all to help me out and I will be very thankful for your support.

With regards
R e h a n

Re: Error while declaring a cursor

R. Giskard Reventlov22-May-12 6:23

R. Giskard Reventlov

22-May-12 6:23

Your syntax appears to be incorrect. Read this[^] to see how to construct a cursor.

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

How to get delete/Update row + SQL

jojoba201118-May-12 22:00

jojoba2011

18-May-12 22:00

Hi,
How can i get the full record that is delete/Update via exec sp_trace_setevent @TraceId

I mean:
I have table with this columns
Id int ,Name nvarchar(max) ,Family nvarchar(max)
with the values:

1,jojoba,alin<br />
2,babk,babyi

...
now i when i update like this:

SQL

UPDATE    tblName
SET              Name='okki', Family='koki' where Id=2

now I wanna this in my trace:

2,babk,babyi Deleted<br />
2,ooki,koki  Inserted

Re: How to get delete/Update row + SQL

Eddy Vluggen19-May-12 0:42

Eddy Vluggen

19-May-12 0:42

Still working on that audit? Smile | :)

Stop wasting time, if your customer really needs the old values of the record, then you'd be best of with doing only inserts; somebody wants to update a record? Mark the current one as outdated (datetime) and insert a new one.

Yes, that'll make your database huge, but logging every action would result in yet even more data, since you're not only storing the new values (ignoring the old) - but also the command.

You best rethink your requirements here.

Bastard Programmer from Hell Suspicious | :suss:

Re: How to get delete/Update row + SQL

jojoba201119-May-12 4:38

jojoba2011

19-May-12 4:38

thanks for your attention!

but its the end of story.
is it possible to get that info via <pre lang="c#">sp_trace_setevent</pre>.
i know that it is in ldf file.

plz give the help to close the question.

Re: How to get delete/Update row + SQL

Eddy Vluggen19-May-12 5:05

Eddy Vluggen

19-May-12 5:05

jojoba2011 wrote:
is it possible to get that info via

Not as far as I can see.

MSDN[^] mentions:

<layer>Text value dependent on the event class captured in the trace. However, if you are tracing a parameterized query, the variables will not be displayed with data values in the TextData column.

jojoba2011 wrote:
i know that it is in ldf file

How did you know?

Bastard Programmer from Hell Suspicious | :suss:

Re: How to get delete/Update row + SQL

jojoba201120-May-12 2:42

jojoba2011

20-May-12 2:42

Thanks for reply!
i have got the row update old value when i go to table and manually change the value "via sp_trace_setevent (Transact-SQL)" but cannot get the old value when using SP for update.

Plz help to get the old value when runing SP

Re: How to get delete/Update row + SQL

Eddy Vluggen20-May-12 3:03

Eddy Vluggen

20-May-12 3:03

A trace shows the commands written to the server; not the values that the server holds. A trace will not show the current values. Executing a SP with some values might even require different permissions than reading those values.

As said, this is the wrong approach. If your customers need the old and the new values, then you should not update or delete, just insert.

Bastard Programmer from Hell Suspicious | :suss:

Re: How to get delete/Update row + SQL

jojoba201120-May-12 17:38

jojoba2011

20-May-12 17:38

I get Old Value and New Value u can see the red color part:OldName,OldFamily--->UpdatedName,UpdatedFamily
via this trace :

SQL

/****************************************************/
/* Created by: SQL Profiler */
/* Date: 16/04/2009 12:29:20 */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 500 
declare @OutputFileName nvarchar(200)
declare @EndTime datetime

SET @OutputFileName = 'C:\STrace' + 
CONVERT(VARCHAR(20), GETDATE(),112) + 
REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')

SET @EndTime = DATEADD(mi,30,getdate())


-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @maxfilesize, @EndTime 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 18, @on

exec sp_trace_setevent @TraceID, 41, 1, @on
exec sp_trace_setevent @TraceID, 41, 6, @on
exec sp_trace_setevent @TraceID, 41, 9, @on
exec sp_trace_setevent @TraceID, 41, 10, @on
exec sp_trace_setevent @TraceID, 41, 11, @on
exec sp_trace_setevent @TraceID, 41, 12, @on
exec sp_trace_setevent @TraceID, 41, 13, @on
exec sp_trace_setevent @TraceID, 41, 14, @on
exec sp_trace_setevent @TraceID, 41, 16, @on
exec sp_trace_setevent @TraceID, 41, 17, @on
exec sp_trace_setevent @TraceID, 41, 18, @on

exec sp_trace_setevent @TraceID, 43, 1, @on
exec sp_trace_setevent @TraceID, 43, 6, @on
exec sp_trace_setevent @TraceID, 43, 9, @on
exec sp_trace_setevent @TraceID, 43, 10, @on
exec sp_trace_setevent @TraceID, 43, 11, @on
exec sp_trace_setevent @TraceID, 43, 12, @on
exec sp_trace_setevent @TraceID, 43, 13, @on
exec sp_trace_setevent @TraceID, 43, 14, @on
exec sp_trace_setevent @TraceID, 43, 16, @on
exec sp_trace_setevent @TraceID, 43, 17, @on
exec sp_trace_setevent @TraceID, 43, 18, @on



exec sp_trace_setevent @TraceID, 45, 1, @on
exec sp_trace_setevent @TraceID, 45, 6, @on
exec sp_trace_setevent @TraceID, 45, 9, @on
exec sp_trace_setevent @TraceID, 45, 10, @on
exec sp_trace_setevent @TraceID, 45, 11, @on
exec sp_trace_setevent @TraceID, 45, 12, @on
exec sp_trace_setevent @TraceID, 45, 13, @on
exec sp_trace_setevent @TraceID, 45, 14, @on
exec sp_trace_setevent @TraceID, 45, 16, @on
exec sp_trace_setevent @TraceID, 45, 17, @on
exec sp_trace_setevent @TraceID, 45, 18, @on



exec sp_trace_setevent @TraceID, 28, 1, @on
exec sp_trace_setevent @TraceID, 28, 6, @on
exec sp_trace_setevent @TraceID, 28, 9, @on
exec sp_trace_setevent @TraceID, 28, 10, @on
exec sp_trace_setevent @TraceID, 28, 11, @on
exec sp_trace_setevent @TraceID, 28, 12, @on
exec sp_trace_setevent @TraceID, 28, 13, @on
exec sp_trace_setevent @TraceID, 28, 14, @on
exec sp_trace_setevent @TraceID, 28, 16, @on
exec sp_trace_setevent @TraceID, 28, 17, @on
exec sp_trace_setevent @TraceID, 28, 18, @on



-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'Microsoft Dynamics NAV client'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go

and the result is :<the red="" color="" item="" is="" the="" one="" that="" i="" wanna="">i get this result when i manually change the item in SQL Management by hand .but i wanna to get this result when running SP.

[Database_Name] ASC,[Table_Schema] ASC,[Table_Name] ASC,[Index_Name] ASC,[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'PK_tblNames',@_msparam_3=N'dbo',@_msparam_4=N'tblNames'UPDATE tblNames SET Name = @Name, Family = @Family WHERE (Id = @Param1) AND (Name = @Param2) AND (Family = @Param3) AND (IdCity = @Param4)exec sp_executesql N'UPDATE tblNames SET Name = @Name, Family = @Family WHERE (Id = @Param1) AND (Name = @Param2) AND (Family = @Param3) AND (IdCity = @Param4)',N'@Name nvarchar(11),@Family nvarchar(13),@Param1 int,@Param2 nvarchar(7),@Param3 nvarchar(9),@Param4 int',@Name=N'UpdatedName',@Family=N'UpdatedFamily',@Param1=2,@Param2=N'OldName',@Param3=N'OldFamily',@Param4=1select col.name, st.name as DT_name, case when (st.name in ('nchar', 'nvarchar') and (col.max_length > 0)) then col.max_length / 2 else col.max_length end, col.precision, col.scale, bt.name as BT_name, col.is_nullable, col.is_identity,col.is_rowguidcol, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed, case when(cmc.column_id is null) then null else cmc.definition end as formular, col.collation_name, col.system_type_id from testii.sys.all_columns col left outer join testii.sys.types st on st.user_type_id = col.user_type_id left outer join testii.sys.types bt on bt.user_type_id = col.system_type_id left outer join testii.sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id = col.column_id left outer join testii.sys.computed_columns cmc on cmc.object_id = col.object_id and cmc.column_id = col.column_id where col.object_id = object_id(N'testii.dbo.tblNames') order by col.column_idselect col.name, st.name as DT_name, case when (st.name in ('nchar', 'nvarchar') and (col.max_length > 0)) then col.max_length / 2 else col.max_length end, col.precision, col.scale, bt.name as BT_name, col.is_nullable, col.is_identity,col.is_rowguidcol, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed, case when(cmc.column_id is null) then null else cmc.definition end as formular, col.collation_name, col.system_type_id from testii.sys.all_columns col left outer join testii.sys.types st on st.user_type_id = col.user_type_id left outer join testii.sys.types bt on bt.user_type_id = col.system_type_id left outer join testii.sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id = col.column_id left outer join testii.sys.computed_columns cmc on cmc.object_id = col.object_id and cmc.column_id = col.column_id where col.object_id = object_id(N'testii.dbo.tblNames') order by col.column_idSET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 1 else 0 end as 'IsCloud'DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 1 else 0 end as 'IsCloud'SELECT
db_name() AS [Database_Name],

Re: How to get delete/Update row + SQL

Eddy Vluggen21-May-12 4:30

Eddy Vluggen

21-May-12 4:30

If you run it manually, you'll see the text as sent to the server. A trace is meant to "trace" what commands are being executed. AFAIK, it wouldn't even remotely interested in the "current" values.

Repeating the question will not change the answer.

Bastard Programmer from Hell Suspicious | :suss:

Re: How to get delete/Update row + SQL

jojoba201121-May-12 20:11

jojoba2011

21-May-12 20:11

thanks for you reply for both C# and Database pages.

But isnt it possible to have when running update SP.

Its very important to do that .
if possible then its end of story.

Q2)is it possible to have the OLD trace if the user delete the .trc file from SQL.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.