|
The first one is definitely better.
You have far more options to tune the statements if you encounter bottlenecks when you have done database modelling as it should be done. The second one leads to several problems. Slow queries, poor maintainability, non-efficient indexing etc.
Just to throw another possibility, you could also consider xml-field on the same table as document names. It can be easily updated, using at client side is typically very straightforward and also it can be indexed.
Hope this helps,
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Awesome.
Thanks for the input.
|
|
|
|
|
You're welcome 
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Most definately agree with Mika, option 1 is the way to go. As for performance, it shouldn't be a big problem unless you expect hundreds of millions of records - in which case you may want to look at partitioned tables.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Good point
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I'm trying to group almost-duplicate contacts in our database.
ie. ABC Corp, ABC Corp., ABC Corp.
The SOUNDEX function only returns a 4-character result. Many of our names are clearly NOT identical, yet return the same result.
Is there a better function I could use?
Cheetah. Ferret. Gonads. What more can I say? - Pete O'Hanlon
|
|
|
|
|
This depends what you are trying to achieve with soundex. If I remember correctly, soundex was created for immigration officers to search for potential names that sound the same, so it's not meant to be an exact match. The algorithm is very simple and you can extend it. However then it may not be as efficient anymore if used for original purpose.
Mika
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Here's a sample output of name with Soundex. The name has spaces stripped out.
There are too many A345.
I looked up the algorithms, and will use D-M I guess.
ATLANTICSHOPPINGCENTRE: A345
ATLANTICSHOPPINGCENTRES: A345
AtlanticPeoplesHousing: A345
ATLANTICSHOPPINGCENTRESLTD: A345
AtlanticBapt.Sr.Citizens: A345
Atlas-AxisLtd.: A342
AtlanticShoppingCentres: A345
ATLANTICSHOP.CENTRE/NEWCO: A345
AtlanticGreatDaneLtd.: A345
AtlanticWholesalers: A345
ATLANTICSHOP.CENTRES: A345
ATL.SHOP.%MELANSONDISPLAY: A342
ATLANTICBALLETTHEATRECOMPAN: A345
ATLANTICWAFFLESLTD.: A345
ATLANTICSAVEEASY: A345
ATLANTICBAPTISTUNIVERSITY: A345
ATLANTICCOMMERCIALPROP.: A345
ATL.SUPERSTORE/A.JEANSSIGNS: A342
AtlanticShoppingCenters: A345
ATLANTICREMANLTD.: A345
AtlanticPeoplesHousingLtd: A345
AtlasColdStorageLtd.: A342
AtlanticUndergroundServices: A345
AtlanticChoiceProperties: A345
Cheetah. Ferret. Gonads. What more can I say? - Pete O'Hanlon
|
|
|
|
|
That sounds fine.
Just a thought: If you're looking for quite exact matches, would it be easiest to create your own function and simply remove (for example using replace) all non-wanted characters like: '[space]%-.: etc. After that convert the string to lower case and using collations strip away accents and so on.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I'm already stripping punctuation and blanks.
Yes I think I have to write my own.
Even D-M only gives 6 digits, so it fails to differentiate my long strings.
Cheetah. Ferret. Gonads. What more can I say? - Pete O'Hanlon
|
|
|
|
|
Just a thought...
If you can afford to let this run as a batch update, maybe you can implement a Levenshtein distance algorithm and decide which records are dupes based on the distance.
|
|
|
|
|
I have created some packages which are using the same sharepoint file. So, when I try to run the jobs (having those packages independently) in parallel, I am not able to execute those. It shows an error stating that failed to acquire connection manager.
What I feel is that as the packages are sharing the same file, those are not able to share the files properly and so the packages are not able to connect to the file.
I just want to know if anyone of you know any changes that I need to make in the sharepoint file properties, in the package properties or in the job properties.
Thank you.
|
|
|
|
|
Dear all,
i am using sql server 2005 , ssrs2005.
i have to generate a report(delivery challan ) where if articles is issued against sample request then one should display as back ground "sample request not for sale" behind the article details
Please help
imran khan
|
|
|
|
|
Hey guys
i need to create a new database using embedded fire bird, at the moment ive got this...
private void btnCreateDatabase_Click(object sender, EventArgs e)
{
if (FieldsValidated())
{
try
{
string fbConnectionString = string.Format("ServerType=1; Dialect=3; User={0}; Password={1}; Database={2};",
txtUserName.Text, txtPassword.Text, txtDbName.Text);
FbConnection.CreateDatabase(fbConnectionString);
DatabaseCreated(this, new CreateFirebirdDbEventArgs(fbConnectionString));
}
catch (FbException ex)
{
MessageBox.Show("btnCreateDatabase_Click - FbException" + Environment.NewLine + ex.ToString());
}
catch (Exception ex)
{
MessageBox.Show("btnCreateDatabase_Click - Exception" + Environment.NewLine + ex.ToString());
}
finally
{
this.Dispose();
}
}
}
... this creates the file just fine, but how do i create the tables in that db?
Thanks
<edit>
I came across a FbBatchExecution object which seems to do the trick
</edit>
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
you.suck = (you.Passion != Programming & you.Occupation == jobTitles.Programmer)
1000100 1101111 1100101 1110011 100000 1110100 1101000 1101001 1110011 100000 1101101 1100101 1100001 1101110 100000 1101001 1101101 100000 1100001 100000 1100111 1100101 1100101 1101011 111111
modified on Wednesday, October 29, 2008 6:31 AM
|
|
|
|
|
how to generate field number by increment (+1) in table mysql using PHP after inserting and deleting data?
ex:
Number
1A (next)
2A
3A
|
|
|
|
|
In SQL Server Management Studio (SSMS), in the Object Explorer, connect to your Database Engine, Databases, Expand your Database Name, Expand Tables, Right - click the Table Name in which you wish to have the increment number and select Modify from the context menu.
You will see the column names in that particular table.
Just select the column where you want the increment number and in the bottom in the Column Properties window, Expand Identity Specification. In the (Is Identity) from the drop down list select Yes and you will see the Identity Increment as 1 by default. That is what you need. If you wish you can change it also to 2 or some other number as you wish.
Hope this helps.
I was to descriptive to answer this simple question, but I am not sure how much do you know about it, so I thought of starting from the scratch.
Good Luck.
|
|
|
|
|
Are those "A"s part of the "number"?  If so, I don't think the system can do it automatically, you'd have to roll your own.
|
|
|
|
|
why you dont declare the column in the design of table auto increamnet ?
|
|
|
|
|
Hi all, i am building a category tree like structure for my FAQ app. i need to show all active categories. The hitch is that
if a parent category is checked as inactive, then the sub or child categories under it should not be displayed even if they
are marked as active. I need a query which will return me such a resultset which i can display in a table. the columns in my
table are categoryid, category, ParentcategoryID,companyID and active. The ParentcategoryID contains id of another category
or 'No parentcategory' which denotes its the main category. It is easy to get the immediate parentcategory and find out if
its active or not. But to find out if that parent category's parent and so on is active or not is tough. please help.
|
|
|
|
|
|
|
hi,
1.first replace ? with @ symbol for all the variables declared with?
2.give ur query like this
select ExtractDigit ('<stringname>')
stringname--> your string name
T.Balaji
|
|
|
|
|
Dear All
in a multi threaded system, I want to access the sql server 2000 data base in 15 separate threads.
But now, when the connection of data base is lost , threads find it separately and they can not make other threads aware of this situation, also it is not possible to organize and manage the data base systematically.
now I want to manage my data base with better mechanisems.
I'd like to use design patterns for this purpose.
But I didn't find any useful information in this regard yet.
May you please let me know any information in this regard.
Thanks in advance
Mahdi
|
|
|
|
|
I'm no stranger to programming, but I am brand new to programming with databases. I use MS Visual C# 2005. I have tried making forms with datasets and table adapters and it looks just fine... that is, until I try to make a change.
I have a simple form with a single datagridview. I edit the records, click the Save button on the binding navigator, the tableadapter.Update() method returns the correct number of updated rows, but when I close the program and re-open it, the changes aren't there.
Am I leaving out something? Is there extra code needed? etc.?
Thanks.
|
|
|
|
|
I am able to pull the correct data in SQLDS1 for the dropdownlist. And, it seems as though SQLDS2 is pulling the correct data in the gridview. However, when selecting from the gridview to populate the detailsview, it only uses the rdb1aa field and i need it to be able to match the selected row data from rdb1aa and rdb2aa with two fields I am selecting from the JOIN in SQLDS3.
If anyone can help, I sure would appreciate it. My code is as follows:
Choose an Area of Study:
<asp:dropdownlist id="ddlPrefix" runat="server" datasourceid="SQLDS1" autopostback="true" datatextfield="rdb1aa" xmlns:asp="#unknown">
<asp:gridview runat="server" id="gvCourse" cellpadding="0" datasourceid="SQLDS2" datakeynames="rdb1aa" autogeneratecolumns="False" font-names="Arial" width="371px" allowpaging="True" pagesize="16" xmlns:asp="#unknown">
<footerstyle backcolor="#D59F0F" font-bold="True" forecolor="White">
<rowstyle backcolor="#FFFBD6" forecolor="#333333">
<pagerstyle backcolor="#D59F0F" forecolor="#333333" horizontalalign="Center">
<selectedrowstyle backcolor="#D59F0F" font-bold="True" forecolor="Navy">
<headerstyle backcolor="#820024" font-bold="True" forecolor="White">
<alternatingrowstyle backcolor="White">
<headerstyle backcolor="#820024" font-bold="True" forecolor="White">
<columns> <asp:commandfield showselectbutton="true">
<asp:boundfield datafield="rdb1aa" headertext="Prefix" readonly="true">
<asp:boundfield datafield="rdb2aa" headertext="Number" readonly="true">
<asp:boundfield datafield="rdi8aa" headertext="Title" readonly="true">
|
<asp:detailsview datasourceid="SQLDS3" cellpadding="2" headerstyle-backcolor="#BDCFE7" headertext="Course Description" autogeneraterows="False" id="dvDesc" runat="server" height="186px" width="164px" forecolor="#333333" gridlines="None" horizontalalign="Center" xmlns:asp="#unknown">
<footerstyle backcolor="#5D7B9D" font-bold="True" forecolor="White">
<commandrowstyle backcolor="#E2DED6" font-bold="True">
<rowstyle backcolor="#F7F6F3" forecolor="#333333">
<fieldheaderstyle backcolor="#820024" font-bold="True">
<pagerstyle backcolor="#284775" forecolor="White" horizontalalign="Center">
<fields>
<asp:boundfield datafield="r3raak" nulldisplaytext="No Description Found">
<headerstyle height="1px" horizontalalign="Left" verticalalign="Top">
<itemstyle font-bold="True" font-size="XX-Small" height="15px" horizontalalign="Left" verticalalign="Top">
<headerstyle backcolor="#D59F0F" font-bold="True" forecolor="White">
Font-Size="Small" Height="1px" HorizontalAlign="Center">
<editrowstyle backcolor="#999999">
<alternatingrowstyle backcolor="White" forecolor="#284775">
|
<asp:sqldatasource id="SQLDS1" connectionstring="<%$ ConnectionStrings:ConnectionString %>" xmlns:asp="#unknown">
SelectCommand="SELECT distinct RDB1AA from (select rdb1aa,rdb2aa from OPENQUERY (as400 , 'select rdb1aa, rdb2aa from shwcmfil.catmsp WHERE RDJHAA = ''A'' AND RDB8AA = ''AC''')) AS derivedtbl_1 ORDER BY RDB1AA" Runat="Server">
<asp:sqldatasource id="SQLDS2" runat="server" connectionstring="<%$ ConnectionStrings:ConnectionString %>" xmlns:asp="#unknown">
SelectCommand="SELECT RDB1AA, RDB2AA, RDI8AA FROM OPENQUERY (as400 , 'select * from shwcmfil.catmsp WHERE RDJHAA = ''A''') AS derivedtbl_1 WHERE (RDB1AA = @rdb1aa) ORDER BY RDB2AA">
<selectparameters>
<asp:controlparameter controlid="ddlPrefix" name="rdb1aa" propertyname="SelectedValue">
<asp:sqldatasource id="SQLDS3" connectionstring="<%$ ConnectionStrings:ConnectionString %>" runat="server" xmlns:asp="#unknown">
SelectCommand="SELECT RDB1AA, RDB2AA, RDI8AA, R3RAAK, R3IXAA FROM OPENQUERY (as400 , 'select * from shwcmfil.catmsp
JOIN shwcmfil.catxtp on (shwcmfil.catmsp.rdb1aa = shwcmfil.catxtp.r3b1aa and shwcmfil.catxtp.r3b2aa = shwcmfil.catmsp.rdb2aa)WHERE RDJHAA = ''A''') AS derivedtbl_1 WHERE (RDB1AA = @rdb1aa)">
<selectparameters>
<asp:controlparameter controlid="gvCourse" name="rdb1aa" propertyname="SelectedValue" type="String">
<asp:controlparameter controlid="gvCourse" name="rdb2aa" propertyname="SelectedValue" type="String">
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
