Running SQL Queries On A Worksheet (With Set Of Random Numbers Between 1-20 Filling Column A) Handled As An…

INTRO

I am using an Excel worksheet as my “database.” No headers so [F1] is assigned to column one by default. I filled the entire column, all 1,048,576 cells with RANDBETWEEN(1,20). I then hard set these values by copy/pasting as value.

It is very slow. Much slower than using Excel functions would be to generate same data. I realize that I could use 1,048,576 as a constant denominator but I wanted to practice with SQL query language, and keep model more dynamic.

EDIT: it should not say % in results not many times. I am getting the % of times each var occurred in my data set. I am basically seeing what the distribution is of randbetween(1-20) outputs are over X calls. In this instance x is 1,048,576.

My SQL Query

"SELECT Round(SUM(IIF([F1]=" & searchKey & ",1,0))*100.0/SUM(IIF([F1]<> Null,1,0)),10) From [Sheet1$];"

Is returning the total number of records in column F1 that are equal to searchKey and dividing that number by the total number of records in column F1.

CODE

Option Explicit
Private Declare PtrSafe Function timeGetTime Lib "winmm.dll" () As Long

Sub SqlQueryOnWorkSheet()
Dim started                                     As Long
Dim cn                                          As ADODB.Connection
Dim filePath                                    As String
Dim counter                                     As Long
Dim outCome                                     As Double
Dim ended                                       As Long

    started = timeGetTime

    filePath = "Z:TestTest1.xlsx"

    Set cn = EstablishConnection(filePath)

    If cn.State <> 1 Then GoTo CleanFail:

    For counter = 1 To 20
        outCome = FindCount(cn, counter)
        PrintOutcome counter, outCome
    Next counter

    cn.Close
    Set cn = Nothing

    ended = timeGetTime
    Debug.Print "QUERIES RAN IN " & (ended - started) / 1000 & " SECONDS"
    Exit Sub
CleanFail:
    Debug.Print "CONNECTION COULD NOT BE MADE"
End Sub

Function EstablishConnection(ByVal filePath As String) As ADODB.Connection
    Set EstablishConnection = New ADODB.Connection
    EstablishConnection.Open _
        "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source='" & filePath & "';" & _
        "Extended Properties=""Excel 12.0 Macro;HDR=No;IMEX=1;"";"
End Function

Function FindCount(ByRef cn As ADODB.Connection, ByVal searchKey As Long) As Double
Dim strSql                                      As String
Dim rs                                          As ADODB.Recordset
On Error GoTo CleanFail:
    Set rs = New ADODB.Recordset
    strSql = "SELECT Round(SUM(IIF([F1]=" & searchKey & ",1,0))*100.0/SUM(IIF([F1]<> Null,1,0)),10) From [Sheet1$];"
    rs.Open strSql, cn
    FindCount = rs.GetString
    rs.Close
    Set rs = Nothing
    Exit Function
CleanFail:
    Debug.Print "QUERY FAILED"
End Function

Sub PrintOutcome(ByVal counter As Long, ByVal outCome As Double)
    Debug.Print "Variable " & counter & " Occured " & outCome & " Many Times"
End Sub

Results

EDIT: THIS SHOULD BE % NOT MANY — FIXED

Variable 1 Occured 4.9837112427 % Of Time

Variable 2 Occured 5.0171852112 % Of Time

Variable 3 Occured 4.9752235413 % Of Time

Variable 4 Occured 4.9716949463 % Of Time

Variable 5 Occured 5.0051689148 % Of Time

Variable 6 Occured 4.9989700317 % Of Time

Variable 7 Occured 4.9901008606 % Of Time

Variable 8 Occured 5.0283432007 % Of Time

Variable 9 Occured 5.0018310547 % Of Time

Variable 10 Occured 5.0164222717 % Of Time

Variable 11 Occured 4.9933433533 % Of Time

Variable 12 Occured 5.0059318542 % Of Time

Variable 13 Occured 5.0333976746 % Of Time

Variable 14 Occured 4.9952507019 % Of Time

Variable 15 Occured 5.0163269043 % Of Time

Variable 16 Occured 4.9654006958 % Of Time

Variable 17 Occured 4.9822807312 % Of Time

Variable 18 Occured 5.0310134888 % Of Time

Variable 19 Occured 5.0113677979 % Of Time

Variable 20 Occured 4.9770355225 % Of Time

QUERIES RAN IN 38.754 SECONDS

All topic

While loop running in thread, but not doing anything [C++]

I am trying to multi-thread my game so that one thread updates and renders the player, while the other listens for inputs. I used a while loop to keep my detached thread running in the background, which is working, but my player isn’t moving even though the code tells it to do exactly that. If I put MessageBox(NULL, "Testing", "Alert", NULL) in Player::update or Player::draw in Player.cpp, the window spams message boxes, which means the code IS running, but the player is NOT moving. Is it a problem with one of the Player functions or with the thread itself?

Window.cpp

#include 
void quit();
void setup(HDC, PAINTSTRUCT, HWND);

const char g_szClassName[] = "myWindowClass";

LRESULT CALLBACK WndProc(HWND hwnd, UINT msg, WPARAM wParam, LPARAM lParam)
{
    switch (msg)
    {
    case WM_CLOSE:
        DestroyWindow(hwnd);
        quit();
        break;
    case WM_DESTROY:
        PostQuitMessage(0);
        break;
    case WM_PAINT:
        PAINTSTRUCT ps;
        HDC hdc;
        hdc = BeginPaint(hwnd, &ps);

        setup(hdc, ps, hwnd);

        EndPaint(hwnd, &ps);
        break;
    default:
        return DefWindowProc(hwnd, msg, wParam, lParam);
        break;
    }
}

int WINAPI WinMain(HINSTANCE hInstance, HINSTANCE hPrevInstance,
    LPSTR lpCmdLine, int nCmdShow)
{
    WNDCLASSEX wc;
    HWND hwnd;
    MSG Msg;

    wc.cbSize = sizeof(WNDCLASSEX);
    wc.style = 0;
    wc.lpfnWndProc = WndProc;
    wc.cbClsExtra = 0;
    wc.cbWndExtra = 0;
    wc.hInstance = hInstance;
    wc.hIcon = LoadIcon(NULL, IDI_APPLICATION);
    wc.hCursor = LoadCursor(NULL, IDC_ARROW);
    wc.hbrBackground = (HBRUSH)(COLOR_WINDOW + 1);
    wc.lpszMenuName = NULL;
    wc.lpszClassName = g_szClassName;
    wc.hIconSm = LoadIcon(NULL, IDI_APPLICATION);

    if (!RegisterClassEx(&wc))
    {
        MessageBox(NULL, "Window Registration Failed!", "Error!",
            MB_ICONEXCLAMATION | MB_OK);
        return 0;
    }

    hwnd = CreateWindowEx(
        WS_EX_CLIENTEDGE,
        g_szClassName,
        "Testing!!!",
        WS_OVERLAPPEDWINDOW,
        CW_USEDEFAULT, CW_USEDEFAULT, 800, 600,
        NULL, NULL, hInstance, NULL);

    if (hwnd == NULL)
    {
        MessageBox(NULL, "Window Creation Failed!", "Error!",
            MB_ICONEXCLAMATION | MB_OK);
        return 0;
    }

    ShowWindow(hwnd, nCmdShow);
    UpdateWindow(hwnd);

    while (GetMessage(&Msg, NULL, 0, 0) > 0)
    {
        TranslateMessage(&Msg);
        DispatchMessage(&Msg);
    }
    return Msg.wParam;
}

Main.cpp

#include 
#include 
#include 
#include "Functions.h"
#include "Player.h"
using namespace std;

void draw(HDC);
void startThreads();
void checkForInput();
bool Quit = false;
HDC hdc;
PAINTSTRUCT ps;
HWND hwnd;
Player player(50, 50);

void setup(HDC hDC, PAINTSTRUCT pS, HWND hWND)
{
    hdc = hDC;
    ps = pS;
    hwnd = hWND;
    startThreads();
}

void checkForInput()
{
    while (!Quit)
    {
        if (GetKeyState(27) & 0x8000)
        {
            Quit = true;
        }
    }
}

void quit()
{
    Quit = true;
}

void draw(HDC hdc)
{
    while (!Quit)
    {
        player.draw(hdc);
        player.update();
        Sleep(16);
    }
}

void startThreads()
{
    thread mm(draw, hdc);
    thread input(checkForInput);
    input.detach();
    mm.detach();
}

Player.cpp

#include 
#include "Player.h"
#include "Functions.h"

Player::Player(int xx, int yy)
{
    x = xx;
    y = yy;
}

void Player::update()
{
    x++;
}

void Player::draw(HDC hdc)
{
    rect(hdc, x, y, 50, 50);
}

Player.h

#pragma once
#include 

struct Player {
public:
    int x;
    int y;

    Player(int, int);
    void update();
    void draw(HDC hdc);
};

Functions.cpp

#include "Functions.h"

void rect(HDC hdc, int x, int y, int w, int h)
{
    Rectangle(hdc, x, y, x + w, y + h);
}

void ellipse(HDC hdc, int x, int y, int w, int h)
{
    Ellipse(hdc, x, y, x + w, y + h);
}

Functions.h

#pragma once
#include 

void rect(HDC, int, int, int, int);
void ellipse(HDC, int, int, int, int);

All topic

Running remote stored procedure with DDL locally

Sorry if my question is redundant, I searched Stack Exchange and the internet for hours but do not have a definitive answer to my question.

Database: SQL Server 2017 + Azure

The goal is to build a BI infrastructure

I have multiple (20+) database servers (clients) which all have one common database in the cloud (Azure). All these clients should be exactly the same when these are on the same DB version. To make life easier when doing an update (almost weekly, tuning), I want my 20+ databases to update automatically.

I thought of the following solution:

  • I create a table in the Azure DB which has the most recent client DB version (e.g. v1.2) check
  • If I have an update script, I will put this as a stored procedure on the Azure DB. check
  • I schedule a task on the client which checks the most recent client DB version once a day and compares whether an update is needed. check
  • If yes -> run the “update”: this is the stored procedure on the Azure DB which have to run on the client DB. oh no

Here I get into trouble, running the stored procedure which resides on the Azure DB obviously runs the stored procedure on the Azure DB. I don’t know how I can run the stored procedure of the Azure DB on the client DB.

Motivation: I thought it would be clever to do it this way so I only have to change the content of the stored procedure and all clients would update itself when getting a connection with the Azure DB.

Also, is this the correct way of tackling this problem? Any help is much appreciated!

If you need more information, please let me know.

All topic

Running Selenium via command line java.lang.class.notFoundexception

I am trying to run a java file from selenium using cmd. Just adding libraries means that the java command (in command lines) does not work (javac works, the file is compiled). Does anyone know any hint?

package folder;

import org.openqa.selenium.By;
import org.openqa.selenium.WebDriver;
import org.openqa.selenium.WebElement;
import org.openqa.selenium.ie.InternetExplorerDriver;
import org.openqa.selenium.support.ui.Select;
public class fileJava{
  public static void main(String[] args){
    System.out.print("Hello World");
  }
}

enter image description here

All topic

What to look for in (trail-)running shoes for various terrain?

I’m (trail-)running quite often and I’m used to wear lightweight standard running sneakers. However, I feel like they are made for the gym or flawless asphalt. The pitty is that I like to run on various surfaces. I run next to the woods, on field tracks (loose rough gravel or just “earth”) and old roads (rough surface with potholes). So, in short, on a trail.

I recognized that some surfaces (especially the gravel) trouble the mentioned shoes pretty much. I feel like the sole is too thin. So, what do I have to look for in “all terrain” running shoes?

All topic

Is running load balanced autoscale apps cheaper on AWS Kubernetes than just using EC2 resources?

We’re running like 100+ servers on AWS EC2 with like 20 microservices all load balanced and autoscaled with 2 to 3 instances.

The cost has become….very significant.

I’m wondering if migrating these to Kubernetes or ECS would be cheaper?

All topic

Use TorBrowser when tor is already running (osx)

I followed a tutorial and now I have Homebrew tor running in the background at all times for use by an unrelated application.

But now I can’t use Tor Browser.

tor connection fails

I typed tor into Terminal and I get this Is Tor already running? message. So I’m guessing only one instance of tor can run at a time.

$ tor
Mar 29 02:32:13.361 [notice] Tor 0.3.2.10 (git-31cc63deb69db819) running on Darwin with Libevent 2.1.8-stable, OpenSSL 1.0.2n, Zlib 1.2.5, Liblzma N/A, and Libzstd N/A.
Mar 29 02:32:13.362 [notice] Tor can't help you if you use it wrong! Learn how to be safe at https://www.torproject.org/download/download#warning
Mar 29 02:32:13.363 [notice] Read configuration file "/usr/local/etc/tor/torrc".
Mar 29 02:32:13.367 [notice] Scheduler type KISTLite has been enabled.
Mar 29 02:32:13.367 [notice] Opening Socks listener on 127.0.0.1:9050
Mar 29 02:32:13.367 [warn] Could not bind to 127.0.0.1:9050: Address already in use. Is Tor already running?
Mar 29 02:32:13.367 [notice] Opening Control listener on 127.0.0.1:9051
Mar 29 02:32:13.367 [warn] Could not bind to 127.0.0.1:9051: Address already in use. Is Tor already running?
Mar 29 02:32:13.367 [warn] Failed to parse/validate config: Failed to bind one of the listener ports.
Mar 29 02:32:13.367 [err] Reading config failed--see warnings above.

I want to use Tor Browser without having to disable my background service. How can I have my cake and eat it too?

All topic

Best strategy for running twice 5km in timespan 2,5 hours

I am participating in a 5km-runing relay event organized by my employer. One of my colleagues dropped out, and couldnt find replacement. Now I offered to run also his 5k.

Running 10k is no problem for me, since I have done that already a couple of times (just below 45 minutes). Normally, in a race you are running the 10k in one go, but due to the nature of the relay race, a colleague suggested that I could take a break of about an hour in between(in which my other teammates run their 5km).

I am in doubt whether taking a break in between is a good idea. On the one hand you can get some rest and refuel for the second 5km, but on the other hand your body will switch to recovery mode during the break.

I am just wondering if there are any studies or experiences that could inform me on this decision? Or any tips for how I should use the break in between?

All topic

First Drag and Drop Actions Triggered when other DragnDrop are Running

Here its my code:

a = driver.Findelement(By.XPath(“Element1”)));
b = driver.Findelement(By.XPath(“Element2”)));
Thread.Sleep(3000); action.ClickAndHold(a).MoveToElement(b).Release().Perform();

This Drag and Drop Action performed well. But when other drag and drop action try to runs, The first action are also Performing. Due to this my test case got failed.

Note: Recently i have done Latest update on Nunit(3.10)

All topic

What is the optimum Q angle for running?

The Q angle is the angle formed by a line drawn from the anterior superior iliac spine through the center of the patella and a line drawn from the center of the patella to the center of the tibial tubercle.

What is the optimum angle for running?

“Normal” Q angle for men is sometimes referenced as 14 degrees or 18 degrees etc, but what is optimum? 0 degrees? Or around 15 degrees?

All topic