Newbie : Efficiency question - performance

Hi,
querying through SQL, wich gives the best result
having to look in to a large table, or looking in smaller tables but with a
lot of joins...
I'm asking this because right now, i have in my design a couple of tables
with only two or three columns, and about 10 records in each.
Would it be more interesting to add these values in my first table, and
create more records in it..Boonaap wrote:
> Hi,
> querying through SQL, wich gives the best result
> having to look in to a large table, or looking in smaller tables but
> with a lot of joins...
> I'm asking this because right now, i have in my design a couple of
> tables with only two or three columns, and about 10 records in each.
> Would it be more interesting to add these values in my first table,
> and create more records in it..
In general, you should use a relational design and only resort to
denormalization if absolutely necessary for performance. So for now I
would suggest you stick to the relational design you have and tune the
query as needed with properly designed indexes. 10 rows of data is so
small that you probably wouldn't have much of a performance issue.
David Gugick
Imceda Software
www.imceda.com|||>> Would it be more interesting to add these values in my first table, and
The number of columns in a table and the number of tables in a database are
not valid criteria for determining good designs. You may want to start by
analyzing the business model and understand the data requirements first
rather than being concerned about performance of a special query with "lots
of joins".
In general, the number of columns in a table and the number of tables in a
schema by themselves are no way related to performance. However, it may be
worthwhile to keep in mind the limitations of the DBMS, its physical
optimization variables etc which can have an effect on the performance on
some queries.
Anith

Newbie - Querying SQL Server Express 2005 database from Excel

Sorry if this is a stupid question, but I created a database using SQl Server Express 2005 and I would like to query one of its tables from an Excel spreadsheet.

Here's what I want to do in pseudo code.

cell A2.value = select OLIGO_ID from table OLIGO where SEQUENCE = 'content of cell D2, a string'

In other words, I want to search the database for a string that is in a cell and retrieve its associated ID number into another cell. I need to do this on many cells.

Any help is appreciated. Thanks.

I guess you can do that with VBA.

As far as SQL is concerned I do something like that to extract data from a cube down to Excel. You will just have to build the connection string for SQL Express (get it from the macro recorder) and adjust the query to a more TSQL like query rather than OLAP. You can build any string you want.

Function to query SQL with a query string

-

Private Sub ado(Connection As String, Query As String, destination As String)
Dim cnnConnect As ADODB.Connection
Dim rstRecordset As ADODB.Recordset

Set cnnConnect = New ADODB.Connection
cnnConnect.Open Connection

Set rstRecordset = New ADODB.Recordset
rstRecordset.Open _
Source:=Query, _
ActiveConnection:=cnnConnect, _
CursorType:=adOpenDynamic, _
LockType:=adLockReadOnly, _
Options:=adCmdText

With ActiveSheet.QueryTables.Add( _
Connection:=rstRecordset, _
destination:=Range(destination))
.FieldNames = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
cnnConnect.Close
Set cnnConnect = Nothing
Set rstRecordset = Nothing
End Sub

-

-- OLAPMENU

OlapMenu = _
"Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=False;Location=analysis.onsemi.com;Initial Catalog=" & InitialCatalog & ""

--

call to the function

Query = "select" & _
"[data Switch].[Switch].members on axis(0)," & _
" Filter( [Region].[Rep Sales Region Desc].members, [Data Switch].[On] >0 ) on axis(1)" & _
"from [ST_Crawl]"
Application.StatusBar = "Now Populating Pull-down Region, please wait..."
Application.Cursor = xlWait
Call ado(OlapMenu, Query, "A3")

|||Yes, I would go as far as say write a function in Excel that accepts the input parameter, then uses ADO to fetch the data. Then you can pass in the value from the spreadsheet into the function, the function takes the parameter and uses an ADODB.Command with params to execute the SQL statement, then the function returns the output as a string.