reduce databse size: design

Showing posts with label design. Show all posts

Friday, March 30, 2012

Regarding Dynamic query...

Hi,

I have basic design question regarding dynamic query,

When we have to build a dynamic query (which has table name also as an input parameter),

->Is it better to write a stored procedure ..?

->directly specify the dynamic query and set the command type as text in .NET code...?

I think,since dynamic queries may not have the advantage of precompliation, it may not yield any performance in using SP's in such case..

Please through some light on this,

TIA

I don't think there's any right answer to this question. Personally I'd prefer to build a stored procedure for this because I like all of my database access code to be centrally located. Others will say it's a waste of time and effort to put this type of dynamic code into a stored procedure because it buys you nothing in terms of performance and it litters the database unnecessarily.

This is the type of topic that can generate a lot of debate. We'll see if anyone else bites. :-)

|||Why do you need to pass in a table name? Why does yourapplication have that much knowledge of your database? It soundsas though you have a serious architectural problem. You'vetightly coupled your application to your database. Figure out howto DECOUPLE the systems so that changing one won't break theother. One of the main benefits of stored procedures isencapsulation; passing in a table name defeats that goal.

sql

Monday, March 26, 2012

Reg Database design

I am wondering whether this is the right forum, if not please provide me the details of the correct forum where I can post this question.

I am trying to design a data base for a document request tracking system. The users request documents as hard copies in various sizes, color/b&W., soft copies in various formats that may be checked out with a chekin date.

Now how do I create the tables for this? My current design is to have a table for users, documents, request. The request table will reference the userid in user table and the document id in document table. This database will be used for a web application that processes 10 documents at a time.

My question is where do I have the fields for the soft copy and hard copy requests?

Can they be in the request table? If a hardcopy is requested then the details such as size, color, no.of copies fields will have values and those corresponding to other fields of the soft copy will be null.(format, estimated return date etc). As I have to fill up the details for 10 documents there will be around 60 -80 fields in the request table. Will it cause any trouble with the maximum allowable row size of sql server 2000?

Or do I need to make them into a separate table? If so how? What will be the fields that the other tables will be referencing?

Thanks

This is not the right forum, no.

Your question is about relational database design theory. If I were you I'd find a good relational databse design text and read it top to bottom before you even think about attempting this. Hunt out Date, Elmasri and Codd on Amazon!

In the nicest possible way...don't try open heart surgery before you've learnt to put a band-aid on!

-Jamie

|||

Thanks for your reply.

First of all I could not find a forum for discussing database design.

Also I have created a database and added tables to it and written stored procedures. I am currently learning to design a data base and the above application was the project I chose from a list of projects. I am trying to understand the relational data theory and design and thought it would be a good idea to discuss it with some body. I thought this forum seemed to be a little advanced so the guys out there could help me with this.

Thanks

Refresh with jsp doesnt work

Hi!

I've created a simple jsp page with a crystal report viewer on it. I used Crystal Reports Developer to design the report, as db I use access and IBM WSED to create and run the jsp file on a server. That works fine, but when I would refresh the report it shows no recordset (and also no error message appears). Why I can't refresh the data?

The code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@. page import="com.crystaldecisions.report.web.viewer.CrystalReportViewer" %>
<%@. page import="com.crystaldecisions.sdk.occa.report.data.*" %>
<%@. page import="com.crystaldecisions.reports.reportengineinterface.JPEReportSourceFactory,
com.crystaldecisions.sdk.occa.report.reportsource.IReportSourceFactory2,com.crystaldecisions.sdk.occa.report.reportsource.IReportSource"
%>
<HTML>
<HEAD>
</HEAD>
<BODY>
CrystalTest.jsp

<%
try {

IReportSourceFactory2 rptSrcFactory = new JPEReportSourceFactory();

String report = "/CrystalReport/Report1.rpt";

Object reportSource = rptSrcFactory.createReportSource(report, request.getLocale())

CrystalReportViewer viewer = new CrystalReportViewer();
viewer.setReportSource(reportSource);

viewer.setOwnPage(true);
viewer.setTop(80);
viewer.setDisplayGroupTree(false);
viewer.setHasLogo(false);
viewer.setHasRefreshButton(true);

viewer.processHttpRequest(request, response, getServletConfig().getServletContext(), null);
viewer.dispose();
} catch(Exception e)
{

out.println("CrystalTest: "+e);
}
%>

</BODY
</HTML>hi there!

I've now solved the problem with a jdbc/db2 connection.

The solution is, that I must connect the Crystal Report with jdbc/db2 to the database and also connect in java/jsp to the database as the follows (only samplecode from helpfile):

setDbLogonViewReport.jsp

<%@. page import= "com.crystaldecisions.report.web.viewer.*,
com.crystaldecisions.sdk.occa.report.data.*" %>
<%@. page import="com.crystaldecisions.report.web.viewer.*" %>
<%@. page import="com.crystaldecisions.sdk.occa.report.data.*" %>
<%@. page import="com.crystaldecisions.reports.reportengineinterface.JPEReportSourceFactory" %>
<%@. page import="com.crystaldecisions.sdk.occa.report.reportsource.IReportSourceFactory2" %>

<%
Object reportSource = session.getAttribute("reportSource");
if (reportSource == null)
{
String report = "/reports/sample.rpt";
IReportSourceFactory2 rptSrcFactory = new JPEReportSourceFactory();
reportSource = rptSrcFactory.createReportSource(report, request.getLocale());
session.setAttribute("reportSource", reportSource);
}

ConnectionInfos connInfos = new ConnectionInfos();
IConnectionInfo connInfo1 = new ConnectionInfo();
connInfo1.setUserName("reportLogin");
connInfo1.setPassword("");
connInfos.add(connInfo1);

CrystalReportViewer viewer = new CrystalReportViewer();

viewer.setReportSource(reportSource);
&n bsp; viewer.setEnableLogonPrompt(false);
viewer.setDatabaseLogonInfos(connInfos);

if (session.getAttribute("refreshed") == null)
{
viewer.refresh();
session.setAttribute("refreshed", "true");
}

viewer.setOwnPage(true);

viewer.processHttpRequest(request, response, getServletConfig().getServletContext(), null);
%>

Ask if you've more questions to that.

Friday, March 9, 2012

reference for error handling

Hi guys,
Do you have complete reference for sql error numbers?
I want to design customize error message, I want to know how to RAISERROR
with correct error number. thanksHi
select * from master.dbo.sysmessages
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:#abp4FQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi guys,
> Do you have complete reference for sql error numbers?
> I want to design customize error message, I want to know how to RAISERROR
> with correct error number. thanks
>
>|||Between this and BOL you should be able to figure it all out:
SELECT *
FROM master..sysmessages
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23abp4FQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Hi guys,
> Do you have complete reference for sql error numbers?
> I want to design customize error message, I want to know how to RAISERROR
> with correct error number. thanks
>
>|||I tried a test, In this case, table4 doesn't exist in database,
but how can I print out this error ? I want to set this error to a
variable,
maybe to store in in error log table later on.
CREATE PROCEDURE testme
AS
select * from table4
return @.@.error
declare @.errornumber int
exec @.errornumber = testme
print @.errornumber
result:
Server: Msg 208, Level 16, State 1, Procedure testme, Line 3
Invalid object name 'table4'.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23XSu5KQIFHA.3332@.TK2MSFTNGP14.phx.gbl...
> Hi
> select * from master.dbo.sysmessages
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:#abp4FQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
RAISERROR
>|||Britney,
Please read Erland Sommarskog's article on error handling so you can get a
handle on batch abortion and other niceties of SQL Server error handling...
http://www.sommarskog.se/error-handling-I.html
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:exLi5RQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
> I tried a test, In this case, table4 doesn't exist in database,
> but how can I print out this error ? I want to set this error to a
> variable,
> maybe to store in in error log table later on.
>
> CREATE PROCEDURE testme
> AS
> select * from table4
> return @.@.error
> declare @.errornumber int
> exec @.errornumber = testme
> print @.errornumber
> --
> result:
> Server: Msg 208, Level 16, State 1, Procedure testme, Line 3
> Invalid object name 'table4'.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%23XSu5KQIFHA.3332@.TK2MSFTNGP14.phx.gbl...
> RAISERROR
>|||I don't think I can set severity 16 error number to a variable.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OVFeyUQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
> Britney,
> Please read Erland Sommarskog's article on error handling so you can get a
> handle on batch abortion and other niceties of SQL Server error
handling...
> http://www.sommarskog.se/error-handling-I.html
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Britney" <britneychen_2001@.yahoo.com> wrote in message
> news:exLi5RQIFHA.2476@.TK2MSFTNGP12.phx.gbl...
>

Wednesday, March 7, 2012

Reefernce dimensions

We have designed a schema which has some snowflakes which we implement as Reference dimensions in SSAS. This design seems to be the most logical given that we have a dimension table called Portfolio, which has a number of related dimensions such as Client, Employee and Product. Instead of direcrly joining all these tables to the Positions fact table, we set them up as Reference dimensions via Portfolio.

I have read in a number of places (Lachev, Mundy) that there may be some issues with this approach, particularly on performance. Are there any metrics on what sort of performance degradation there may be? Does it depend on the number of Reference dimensions? Is it generally better if possible to always use the Standard dimensions by denormalising to a star schema - that is making Client, Employee and Product join directly to the fact table? Are there any other advantages or disadvantages, such as in the ease of data maintenance?

I would be grateful for any feedback on this.

I don't know what the performance implications of Reference dimensions are, other than that you should select the "Materialize" option on the Usage dialog, to improve query peformance - could you mention the respective page numbers in the 2 books, since many in this forum may have access to them?

But, regardless of whether you use Reference dimensions, or roll Client, Employee and Product into the Portfolio dimension, there should be no need to denormalize the snowflake to a star. For example, the Product dimension in Adventure Works has 3 tables, but there are other examples of Reference dimensions as well.